How to add unnest conditions based on array length

SQL++
1

I have array structures similar to below
1st:
{
“x” : [

],
"y" : "2"

}

2nd:
{
“x” : [
{
“a” : “2”,
“b” : “3”
}
],
“y” : “2”
}

If length of x is >1, then use unnest and check if a=2 and y=2. Else check only for y=2

2 
SELECT *
FROM default AS d
WHERE  d. y = 2 AND  (ARRAY_LENGTH(d.x)  < =  1 OR  ANY v IN d.x   SATISFIES v.a =2 END);

OR

SELECT *
FROM default  AS d
UNNEST d.x AS ux
WHERE d.y = 2  AND  (ARRAY_LENGTH(d.x)  < =  1 OR  ux.a = 2 );
3

I’m looking for a single query to handle both the cases @vsr1

4

Based on what is your output requirement use one of the query

5

For the first time, my array will be empty. Objects will be pushed into it later.
In this case, I cannot use two different queries right?
I need unnest only if array has elements and no unnest if array is empty. Is this possible?

6 
SELECT *
FROM default  AS d
LEFT UNNEST d.x AS ux
WHERE d.y = 2  AND  (ux IS MISSING  OR ARRAY_LENGTH(d.x)  < =  1 OR  ux.a = 2 );
7

Thanks, works perfectly. @vsr1

But if the json is like below, and if I have to unnest both x and y, output is being duplicated based on the length of those two arrays. Is it possible to join two unnest statements?

{
“x” : [
{
“a” : “2”,
“b” : “3”
}
],
“y” : “2”,
“z” : [
“c” : “1”,
“d” : “2”
]
}

8

You can use many UNNESTs. If you don’t want Unnest use ANY clause. You can also Unnest in parent query. In this case y is not ARRAY you can’t UNNEST.