ARRAY_UNION on ARRAY FROM Objects

SQL++
1

Hi guys,

I need your help another time.

I’ve a document structure as following:

…
“languageInfos”: [
{
“language”: “en”
},
{
“language”: “el”
},
{
“language”: “en”
},
{
“language”: “el”
},
{
“language”: “en”
},
{
“language”: “ru”
}
],
…

What I want to get is one array with all existing languages in all documents.

So, for now, I’m getting the values as an array with the query:

select raw ARRAY l.language for l in languageInfos END from default

The result is:

[

[
“en”,
“el”,
“en”,
“el”,
“en”,
“ru”
],
[
“en”,
“el”,
“pl”,
“ru”,
“sr”,
“hu”
],
[
“en”
],
…
]

So my Problem is now that I try to put ARRAY_UNION around which I can’t manage right now.

Maybe this is also the wrong approach to achieve this and there is a better way?

Any help is welcome.

cheers
Kirsten

2

SELECT ARRAY_FLATTEN(ARRAY_AGG(languageInfos[*].language),2) FROM default;

If you want unique entries

SELECT ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY_AGG(languageInfos[*].language),2)) FROM default;

OR
SELECT li.language FROM default AS d UNNEST d.languageInfos AS li;
SELECT DISTINCT li.language FROM default AS d UNNEST d.languageInfos AS li;

3

@vsr1 On more time thanks a lot.