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.

[

[
“ba3bc260”,
“a9c54712”,
“02150f2f”,
“3e7b0029”,
“b42cba48”,
“a799c190”,
“cac57090”,
“b4bc6214”,
“a2ffa339”,
“21a5fc9f”,
“09f0efc0”,
“f9131c4a”,
“587fd40d”,
“4b86d339”,
“0791591d”,
“03dcd44a”,
“4520b316”,
“05e7d708”,
“463345e5”,
“d2ee0b0e”,
“0bd14862”,
“d6d513c3”,
“a56f8d53”,
“81f69f9d”,
“65e35509”,
“12aca702”,
“102111e7”,
“0b9a1882”,
“84670b61”,
“ce3355fc”,
“64434d18”,
“273dd591”,
“40732b07”,
“cd09eef9”,
“9a1e3ad2”,
“e8355d10”,
“768fbd0e”,
“3c1006ad”,
“e5311913”,
“e24a5708”,
“31ec97ea”,
“df7d19ea”,
“f9f380c8”,
“9c8bac04”,
“4efd092b”,
“407f0001”,
“73b29331”,
“f3b391ea”,
“b3b922f5”,
“aae6e939”,
“4bc4e46f”,
“d18e7cca”,
“36b65ae5”,
“16c54fcb”,
“6007ef39”,
“3c537f5a”,
“c51c06b4”,
“a68c8b1c”,
“a5125fc6”,
“ea8dfac1”,
“dd0d5caf”,
“67811ef4”,
“e452b54f”,
“cf73e04d”,
“c3ae74e5”,
“26750fce”,
“d19cf53b”,
“7524ee66”,
“cbc5413e”,
“796251fa”,
“bafa478b”,
“6de73576”,
“c708048f”,
“8f62a9df”,
“41540289”,
“c45d212d”,
“31bf3878”,
“14db8b50”,
“2761015b”,
“2e5e1d2a”,
“05d4a593”,
“2a4ba637”,
“3be499e2”,
“4f2dc6ba”,
“57f6f803”,
“e97077c9”,
“ebf24de9”,
“53e24703”,
“8f351ee9”,
“063397e7”,
“80f8efb6”,
“bd640638”,
“1e35c707”,
“b0d7f084”,
“694625c0”,
“bc223d44”,
“0158c1f3”,
“1ab61212”,
“59a0225a”,
“64e00d87”,
“22315772”,
“97313cb5”,
“360cf3c7”,
“8eb6315e”,
“928dce5a”,
“2ba64bb9”,
“9993b938”,
“a186f1ae”,
“c3b0046d”,
“03a37806”,
“3b0ac5b3”
]
]

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

cheers
Kirsten

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" } ] } ] });

SELECT   ARRAY v FOR v IN dv WHEN ARRAY_LENGTH(v.services) > 0 END
FROM default AS d
LET dv = ARRAY OBJECT_PUT(v,"services", ARRAY s FOR s IN v.services WHEN s.id 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.

cheers
Kirsten

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.

cheers
Kirsten

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.