Getting Object of arrays of similar keys from objects in multiple documents

Hi, I have a question regarding the use of ARRAY_STAR, given something similar to this stored in my couchbase:

{
    "id": 1.
    "other_attr" : "some_value",
    "compoundKey": {
          "key_1": "a",
          "key_2": "b",
          "key_3": "c"
     }
 },
 {
   "id": 2,
    "other_attr" : "some_value",
    "compoundKey": {
          "key_2": "c",
          "key_3": "d",
          "key_4": "e"
     }
 }
]

How can I achieve something like this?

{
   compoundKey: {
     "key_1": ["a"],
     "key_2": ["b", "c"],
     "key_3": ["c", "d"],
     "key_4": ["e"]
   }
}

I found the documentation on ARRAY_STAR and tried to utilize it in the following query, but it doesnt seem to be working:
SELECT ARRAY_STAR(compoundKey) from dataset → this returns an array of null objects

The ARRAY_STAR function appears to work when assigning the document list as a variable, e.g.

LET ARR = [
 {
    "compoundKey": {
          "key_1": "a",
          "key_2": "b",
          "key_3": "c"
     }
 },
 {
    "compoundKey": {
          "key_2": "c",
          "key_3": "d",
          "key_4": "e"
     }
 }
]

SELECT ARRAY_STAR(ARR)

May I know if there is a way to write a query that extracts out the array of compoundKey objects and apply ARRAY_STAR to the result?

compoundKey is Object not ARRAY to use ARRAY_STAR.

SELECT OBJECT v.name:v.av FOR v IN ( SELECT op.name, ARRAY_AGG(op.val) AS av
                                     FROM mybucket b
                                     LET  op = OBJECT_PAIRS(b.compoundKey)
                                     WHERE IS_OBJECT(b.compoundKey )
                                     GROUP BY op.name) END AS compoundKey;

NOTE: It is grouping operation, may take time and required memory if operation is on huge dataset.

1 Like

Thanks for the input! I managed to get the result I wanted with your query and some slight modificications. End result was

SELECT VALUE {name: av} FROM ( SELECT op.name,  ARRAY_DISTINCT(ARRAY_AGG(op.`value`)) AS av
                                     FROM mybucket b
                                    UNNEST OBJECT_PAIRS(b.compoundKey) as op
                                     WHERE IS_OBJECT(b.compoundKey )
                                     GROUP BY op.name) AS compoundKey;
SELECT   VALUE {op.name: ARRAY_DISTINCT(ARRAY_AGG(op.`value`)) }
FROM mybucket b
UNNEST OBJECT_PAIRS(b.compoundKey) as op
WHERE IS_OBJECT(b.compoundKey )
 GROUP BY op.name
1 Like