Array indexing in n1ql

SQL++
,
1

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?

2

Same comment I made on Stack Overflow. I don’t see m2 in your data…

3

Sorry I meant SELECT z FROM data x UNNEST x.data y UNNEST y.country z;

4

Add LIMIT 1 at the end.

5

What if I want to retrieve only the Nth country from the list?

6

Add LIMIT 1 OFFSET N - 1;

7

Thanks. Can you tell me why z[0] doesn’t work in this case?

8

What would z[0] represent? N1QL is returning a list of results, each of which has a single z.

9

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.

10

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.