I ran this query: SELECT z FROM data x UNNEST x.m2 y UNNEST y.country z;
on this json document:
{
"data": {
"country": [
{ "name": "Canada" },
{ "name": "Greece" }
]
}
}
And got this result:
[
{
"z": {
"name": "Canada"
}
},
{
"z": {
"name": "Greece"
}
}
]
I want to select the first country name (Canada) and tried doing this:
SELECT z[0] FROM data x UNNEST x.m2 y UNNEST y.country z;
but that just return empty results. Any idea how to do it?
Same comment I made on Stack Overflow. I don’t see m2 in your data…
Sorry I meant SELECT z FROM data x UNNEST x.data y UNNEST y.country z;
What if I want to retrieve only the Nth country from the list?
Add LIMIT 1 OFFSET N - 1;
Thanks. Can you tell me why z[0]
doesn’t work in this case?
What would z[0] represent? N1QL is returning a list of results, each of which has a single z.
Maybe I am not understanding the meaning of unnest
, but I thought
data x UNNEST x.data y UNNEST y.country z
would flatten the entire document into
[data: { country: { name: Canada } }
data: { country: { name: Greece} } ]
and hence I can select the first or second element out of it.
No. I would suggest you try a few UNNESTs and look at the results, as well the counts of the results. It is the best way to understand UNNEST.