Select SubDocuments based on array

Hi guys,

me again.
Maybe someone has a idea for me.

I have one documents in the bucket which holds a structure for available services groupd by a category. Structure like the following.


“id”: “172926d9”,
“services”: [
“id”: “cac57090”,
“service”: “Immobilienmakler”,
“type”: “service”
“id”: “cbc5413e”,
“service”: “Architekt”,
“type”: “service”
“id”: “a9946e2f”,
“service”: “Interior Designer”,
“type”: “service”
“id”: “172926d9”,
“services”: [
“id”: “cac57090”,
“service”: “Real Estate Agent”,
“type”: “service”
“id”: “cbc5413e”,
“service”: “Architect”,
“type”: “service”
“id”: “a9946e2f”,
“service”: “Interior Designer”,
“type”: “service”


This are assigned in other documents like that

          "serviceInfo": [
          "category": "c14a52e7",
          "service": "d6d513c3"

I have already a query which has the whole assigned services in one array from all documents where services are assigned.



WHat I want to achive know is to get just the entries from the first document where this service Ids are present in the services, but with the accordigly structure, meaning I want tho keep the hierachy but reduced on non existing services in the assigned documents.
Hope that was understandable.

Any idea for that, or do I have to cahnge the structure of document one for this to have the services separated in documents?

Any help are welcome. THanks


INSERT INTO default VALUES ("dk01",{"a": [ { "id": "172926d9", "services": [ { "id": "cac57090", "service": "Immobilienmakler", "type": "service" }, { "id": "cbc5413e", "service": "Architekt", "type": "service" }, { "id": "a9946e2f", "service": "Interior Designer", "type": "service" } ] }, { "id": "172926d9", "services": [ { "id": "cac57090", "service": "Real Estate Agent", "type": "service" }, { "id": "cbc5413e", "service": "Architect", "type": "service" }, { "id": "a9946e2f", "service": "Interior Designer", "type": "service" } ] } ] });

FROM default AS d
LET dv = ARRAY OBJECT_PUT(v,"services", ARRAY s FOR s IN WHEN IN ARRAY_FLATTEN([[ "cac57090", "a9946e2f", "cac57090"]],2) END)
                     FOR v IN d.a END;

ARRAY_FLATTEN() – Flattens array , The first argument can be expression or subqeury
Inner array construct (second argument to OBJECT_PUT() ) removes the entires not needed. This can be end up empty array if no match
OBJECT_PUT() replaces services with new value.
Projection removes empty services .

Hi @vsr1,

one more time thanks a lot.
Sorry for my late reply, I was extremely busy the last days.


Hi @vsr1,

now I just have one issue. In your example you have created a document with a as main key and all category Objects in an array.
Unfortunately I don’t have such a key in my existing document. So, I tried your example but assign d instead of d.a to v. But this dosen’t work in this case dv is always null.
Any chance to get the example running without a key for the Array?
I guess the Problem in this case now is the automatically added alias for the document, with select RAW d it looks good but I think this dosen’t work in the FOR statement.


How do u get your array of objects. Replace d.a with that.
It can be expression or subquery or field in document.

Ah yes I see. Stupid me, I was thinking in a wrong direction. Thank you.