Couchbase Query to get data on a nested level property & Create index

Question

How to query nested document values in couchbase n1ql in java?

Document value is an object (Person) with 1 of its property type a List of objects (addresses).

Address object has multiple properties like city, zipCode, etc..

How to write a query to find matching records based on the lowest level (like city or zipCode)

Below is a sample document structure, all the documents have a similar structure

{
    companyName: "ABC Inc",
    companyId: 123,
    persons: [
        {
          "name": "John Smith",
          "address": [
                {
                    "city": "London",
                    "zipCode": 1234,
                    "street": "23 Nottingham dr",
                    "contact": "123456678"
                },
                {
                    "city": "Los Angeles",
                    "zipCode": 67834,
                    "street": "345 Richmond dr",
                    "contact": "23456"
                }
            ]
        },
        {
          "name": "Peter Drink",
          "address": [
                {
                    "city": "Delhi",
                    "zipCode": 70046,
                    "street": "SP Road",
                    "contact": "9844334"
                },
                {
                    "city": "Los Angeles",
                    "zipCode": 67834,
                    "street": "433 Ku St",
                    "contact": "12345677"
                }
            ]
        }
    ]
}

the input criteria is zipCode, I need to be able to get all the person objects across all the documents based on the zipCode

Input: zipCode 67834 Expected Output:

[
    {
        "city": "Los Angeles",
        "zipCode": 67834,
        "street": "433 Ku St",
        "contact": "12345677"
    },
    {
        "city": "Los Angeles",
        "zipCode": 67834,
        "street": "345 Richmond dr",
        "contact": "23456"
    }
]

Answer 1

You need to first UNNEST the arrays (This makes self join parent document with each document of array)

SELECT a.*
FROM default AS d
UNNEST d.persons AS p
UNNEST p.address AS a
WHERE a.zipCode = 67834 AND .....;


CREATE INDEX ix1 ON default(DISTINCT ARRAY (DISTINCT ARRAY a.zipCode FOR a IN p.address END) FOR p IN persons END);

Binding variable names in the index must match with Unnest alias names to pick the index.