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
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
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 );
Based on what is your output requirement use one of the query
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?
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 );
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”
]
}
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.