Couchbase Query Capability to Return a Part of Array from A Document

SQL++
1

Hi @vsr1,

We have a requirement to pick up only a set of entries from under an array inside a document but also get all other variable from the same doc.

“person”:{
“name”:“User_1”,
“addresses”: [
{“city” : “a”},
{“city” : “b”},
{“city” : “c”},
{“city” : “d”}
],
“age” : 26
}

The requirement is to get the Full Doc and the query should take an additional limit and offset like offset 0 and limit 2 should return the below data (partial for addresses variable)

“person”:{
“name”:“User_1”,
“addresses”: [
{“city” : “a”},
{“city” : “b”}
],
“age” : 26
}

and then for offset 2 and limit 2 must return the rest of the data like
“person”:{
“name”:“User_1”,
“addresses”: [
{“city” : “c”},
{“city” : “d”}
],
“age” : 26
}

2

You would simply list your fields and use array sub-scripting (slicing) to return portions of the array, advancing as you would with offsets and limits:

select person.name, person.addresses[0:2] addresses, person.age from { "name":"User_1", "addresses": [ {"city" : "a"}, {"city" : "b"}, {"city" : "c"}, {"city" : "d"} ], "age" : 26 } person;

Yields:

    "results": [
    {
        "addresses": [
            {
                "city": "a"
            },
            {
                "city": "b"
            }
        ],
        "age": 26,
        "name": "User_1"
    }
    ],

And:

select person.name, person.addresses[2:4] addresses, person.age from { "name":"User_1", "addresses": [ {"city" : "a"}, {"city" : "b"}, {"city" : "c"}, {"city" : "d"} ], "age" : 26 } person;

yields:

    "results": [
    {
        "addresses": [
            {
                "city": "c"
            },
            {
                "city": "d"
            }
        ],
        "age": 26,
        "name": "User_1"
    }
    ],

If you go beyond the bounds of the array, the result of the sub-scripting is MISSING so you may want an initial query using ARRAY_LENGTH() to determine the upper limit.

Ref:

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/nestedops.html#array-slicing

HTH.

3

There are many other fields in the document (this is sample, I have 100 other fields in actual one), and this way I will have to list all, tomorrow if new field is added , will have to update the query.

Any Syntax which can pull other fields and addresses part is only what is required.

4

Yes, override the element at the end of the projection list (correctly aliased):

select person.*, person.addresses[1:3] addresses from { "name":"User_1", "addresses": [ {"city" : "a"}, {"city" : "b"}, {"city" : "c"}, {"city" : "d"} ], "age" : 26 } person;

yields:

    "results": [
    {
        "addresses": [
            {
                "city": "b"
            },
            {
                "city": "c"
            }
        ],
        "age": 26,
        "name": "User_1"
    }
    ],

HTH.

(I would add that if your application isn’t using all the fields then this is less efficient in terms of transfer than listing the desired fields and updating the query when the application should be aware of a change in the fields list.)

3 Likes
5

Also use subquery expression and use full functionality of SELECT

SELECT d.*, 
                (SELECT sd.* 
                  FROM d.address AS sd
                  WHERE ..............
                   OFFSET 2
                   LIMIT 3) AS address
FROM default AS d 
WHERE .......;