N1QL Query assistance

I have multiple documents that looks like this

{
  "id": "1",
  "mappings": {
    "A": [
      {
        "code": "986408"
      }
    ],
    "B": [
      {
        "code": "129264"
      }
    ],
    "C": [
      {
        "code": "10cd76"
      },
      {
        "code": "1d5aae"
      }
    ]
  }
  "type": "com.test.doc",
}

I want a query that will give me all codes grouped by key (across all documents), so for example

{
“A”: [“986408”, “valueInAnotherDocument”],
“B”: [“129264”],
“C”: [“10cd76”, “1d5aae”]
}

Is this possible?

Thanks,

SELECT OBJECT o.name:o.code FOR o IN (SELECT op.name,  ARRAY_AGG( ov.code) AS code
                  FROM default AS d
                  UNNEST OBJECT_PAIRS(d.mappings) AS op
                  UNNEST op.val AS ov
                  WHERE d.type = "com.test.doc"
                  GROUP BY op.name) END;
1 Like

Thanks a lot, it works just fine. Couldn’t have done it without your help.

Does this need an index to help with performance? If so, can you give me the index for it?

Thank you again

As you don’t have any predicates based on predicates create index. In this case type.
https://blog.couchbase.com/create-right-index-get-right-performance/

1 Like