Querying a nested object, but ignoring its variable parent

SQL++
,
1

Hi all,

I’m trying to query a specific repeating json object tree within a variable parent structure (with a static parent over THAT parent). I am stuck. I have tried using node.field, UNNEST, and use ARRAY… I’m not super good with N1QL so I’m probably doing something wrong. Any help is appreciated

Query Sample:
select *
from bucket as sc
where meta(sc).id = ‘somekey’

Document sample:
[
{
“sc”: {
“property_id”: 123,
“amenities”: {
“361”: {
“id”: 361,
“name”: “Breakfast available (surcharge)”
},
]
Desired output:
{
“property_id”:123,
“amenities”:
[“Breakfast available (surcharge)”,
“Laundry facilities”]
}

2

Can you fix desired output
{ “name”: “Breakfast available (surcharge)”,
“name”:“Laundry facilities”
}
Is not valid JSON. name is repeated and overwrite.

3

I was also going to code around this in C# but I run into this additional issue that confuses me even more.

select
am.property_id,
am.amenities.361.id
,am.Amenities.361.id.name
from bucket am
where meta(am).id = ‘somekey’

The above query doesnt return name, just propertyID and ID

[
{
“id”: 361,
“property_id”: “123”
}
]

4

@vsr1

Fixed the desired output. Dont know where my head was there…

“amenities”: [“item1”,“item2”]

5 
SELECT sc.*, ARRAY  v.val.name FOR v IN  OBJECT_PAIRS(sc.amenities) END AS amenities
from  `bucket`  as sc
where meta(sc).id = "somekey";

SELECT  sc.property_id, sc.`321`.id AS id1, sc.`321`.name 
from  `bucket`  as sc
where meta(sc).id = "somekey";

If you are doing search of document key USE KEYS will be better.

  SELECT sc.*, ARRAY  v.val.name FOR v IN  OBJECT_PAIRS(sc.amenities) END AS amenities
    from  `bucket`  as sc USE KEYS "somekey";
6

Amazing, worked great! What is v.val? What part of this query “skipped” the variable node, I want to read the documentation on the function/method

7

Basically your object has dynamic filed names. If you don’t know how construct path you can use OBJECT_PAIRS() which gives ARRAY of OBJECTS name, val pairs. Then you can use ARRAY collection to apply required dynamic field.

Check this out https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html#object_pairsexpression
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/collectionops.html

1 Like