Query multiple values of a same Key in a nested structure

Hi all,

I would like to know how I can query a particular value from a nested structure that has the same key
Here is a sample nested structure like a dictionary.

I want to query code for all item from product

"products":{
	"itemA":{
		"price" : $10,
		"code": 123,
		"importTime": "2000/01/01"
	},
	"itemB":{
		"price" : $10,
		"code": 456,
		"importTime": "2010/01/01"
	},
	"itemC":{
		"price" : $10,
		"code": 789,
		"importTime": "2020/05/12"
	}
}

This is what I’ve tried in command line.

SELECT OBJECT_VALUES(products)[*].code FROM bucket_name

Returns

[
  {
    "code": [
      123,
      456,
      789
    ]
  }
]

I’m looking for a returned result as below

[
  {
    "itemA":{
		"code": 123,
	},
	"itemB":{
		"code": 456,
	},
	"itemC":{
		"code": 789,
	}
  }
]

Thanks in advance.

1 Like
INSERT INTO default VALUES ("f01",{"products":{ "itemA":{ "price" : 10, "code": 123, "importTime": "2000/01/01" }, "itemB":{ "price" : 10, "code": 456, "importTime": "2010/01/01" }, "itemC":{ "price" : 10, "code": 789, "importTime": "2020/05/12" } }});
SELECT  nobj.* FROM default d USE KEYS "f01"
LET nobj = OBJECT v.name:{v.val.code} FOR v IN OBJECT_PAIRS(d.products) END;

If you want retrieve code and it item any where in document you can also try this

SELECT  nobj.* FROM default d USE KEYS "f01"
LET nobj = OBJECT v.name:{v.val.code} FOR v WITHIN OBJECT_PAIRS(d.products)
                  WHEN v.name IS NOT MISSING AND v.val.code IS NOT MISSING END;