lenix
February 20, 2019, 12:39pm
1
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,
vsr1
February 20, 2019, 2:45pm
2
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
lenix
February 20, 2019, 3:16pm
3
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
vsr1
February 20, 2019, 3:36pm
4
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