Count and UNNEST with multiple matches in condition

query

#1

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


#2
SELECT  d.serviceInfos, d.languageInfos,  d.id
FROM default AS d
WHERE ANY l IN d.languageInfos SATISFIES l.language IN ["en","it"] END 
                 AND ANY s IN d.serviceInfos SATISFIES l.service = "e8355d10" END;

FYI: UNNEST is self join of original document with documents in array. So it can produce 1:m documents. If you really need to use UNNEST you can do GROUP BY or want to do count COUNT(DISTINCT x)


#3

Thanks a lot @vsr1 works perfect.