Unexpected behavior required (data nested in arrays) for N1QL Queries

SQL++
1

For the following document:

confusion::1
{
    "people": [
        "person": {
            "name": {
                "firstName": "John",
                "lastName": "Doe"
            }
        }
    ]
}

The following index:

CREATE INDEX `nested_confusion_idx`
ON `data1` (meta().`id`,
     ARRAY
         [person.name.firstName,
          person.name.lastName
         ]
    FOR reg IN people END)
WHERE meta().`id` LIKE "confusion::%"
USING GSI;

The following query does not work as expected:

SELECT meta().`id`, people[*].person.name.firstName
FROM data1
WHERE ((meta().`id`) like "confusion::%")

Yet, this query has changes [ * ] on every element in the path to get the desired results:

SELECT meta().`id`, people[*].person[*].name[*].firstName
FROM data1
WHERE ((meta().`id`) like "confusion::%")

Upon further experimentation…

This also works:

SELECT meta().`id`, people[*].person[0].name.firstName
FROM data1
WHERE ((meta().`id`) like "confusion::%")

And so does this:

SELECT meta().`id`, people[0].person.name.firstName
FROM data1
WHERE ((meta().`id`) like "confusion::%")

But, of course, this does not:

SELECT meta().`id`, people[0].person[0].name[0].firstName
FROM data1
WHERE ((meta().`id`) like "confusion::%")

And, when it works, the response isn’t fully qualified person.name.firstName, it’s just firstName

2

All the queries are working as expected.

1)   In projection when used  dotted expression  path(person.name.firstName) the result will inherit last field name in dotted path i.e firstName
2) When people[*].person it returns array objects of persons if you need further  dotted you need  again [*]

To Better understand SELECT people[*] FROM ... and see how out put structure look. If it is ARRAY u need to uses subscript or *

You can also use ARRAY constructions like follows instead of [*]
Examples:

ARRAY  v.person.name.firstName FOR v IN people END   -- ARRAY of values
ARRAY  v.person.name.firstName FOR v IN people WHEN v.person.name.lastName = "Doe" END -- Array of values by filtering
ARRAY  { v.person.name.firstName } FOR v IN people WHEN v.person.name.lastName = "Doe" END -- array of objects by filtering

The following query should use covering index nested_confusion_idx (added create index again because reg variable qualification is missing)

CREATE INDEX `nested_confusion_idx`
ON `data1` (meta().`id`,
     ARRAY
         [reg.person.name.firstName,
          reg.person.name.lastName
         ]
    FOR reg IN people END)
WHERE meta().`id` LIKE "confusion::%"
USING GSI;

SELECT meta().`id`,   ARRAY
         [reg.person.name.firstName,
          reg.person.name.lastName
         ]
    FOR reg IN people END AS names
FROM data1
WHERE ((meta().`id`) like "confusion::%");
1 Like
3

Thanks for the thorough response!