Hi,
I have a problem to get the right count value in a query and I assume my problem is the multi-match in the condition for the unnest.
I’ve tried to shrink the documents down to only the necessary structure, in my case I try to count a different value which is not part of this structure here, but I guess for the issue it doesn’t matter.
Let’s assume the following documents.
[ { "id": 198, "languageInfos": [ { "language": "en" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 217, "languageInfos": [ { "language": "en" }, { "language": "ja" }, { "language": "it" }, { "language": "es" }, { "language": "fr" }, { "language": "tr" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 263, "languageInfos": [ { "language": "en" }, { "language": "es" }, { "language": "it" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 348, "languageInfos": [ { "language": "el" }, { "language": "ru" }, { "language": "it" }, { "language": "ru" }, { "language": "en" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 370, "languageInfos": [ { "language": "en" }, { "language": "ja" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }]
So, if I run the following query
Select serviceInfos, languageInfos, id
from default UNNEST(ARRAY_DISTINCT(serviceInfos[*].service)) s
UNNEST(ARRAY_DISTINCT(languageInfos[*].language)) l
where s = "e8355d10"
and l in ['en','it']
For this, I get the following result.
[ { "id": 198, "languageInfos": [ { "language": "en" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 217, "languageInfos": [ { "language": "ja" }, { "language": "tr" }, { "language": "es" }, { "language": "en" }, { "language": "it" }, { "language": "fr" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "b42cba48" }, { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 217, "languageInfos": [ { "language": "es" }, { "language": "ja" }, { "language": "tr" }, { "language": "fr" }, { "language": "en" }, { "language": "it" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "b42cba48" }, { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 263, "languageInfos": [ { "language": "en" }, { "language": "es" }, { "language": "it" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 263, "languageInfos": [ { "language": "it" }, { "language": "en" }, { "language": "es" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 348, "languageInfos": [ { "language": "ru" }, { "language": "it" }, { "language": "en" }, { "language": "el" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 348, "languageInfos": [ { "language": "el" }, { "language": "ru" }, { "language": "it" }, { "language": "en" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }, { "id": 370, "languageInfos": [ { "language": "ja" }, { "language": "en" } ], "serviceInfos": [ { "category": "1caa0cba", "service": "e8355d10" } ] }]
I can avoid the duplicate response by distinct, but as soon as Ido a count on id, I get the wrong count which is the count of the above-shown result.
So I guess the issue is the condition l in ['en,'it']
which simply returns the same id for each match. Is it possible to avoid this?
All suggestions and help are welcome.
cheers
Kirsten