Nest only certain fields

SQL++
1

When using NEST, is there a way to select fields ?

Document: master

{
    "title": "test",
    "items": [
        {
            "id": 1
        },
        {
            "id": 2
        }
    ]
}

Documents to be nested


Document: 1

{
    "attribute1": "test",
    "attribute2": "test2"
}


Document: 2

{
    "attribute1": "test",
    "attribute2": "test2"
}

Query:

select bucket1.* from bucket bucket1 use keys ['master'] nest bucket nested on keys ARRAY x.id for x in bucket1.items END

I would like to nest only “attribute1” field.

Desired output

{
    "title": "test",
    "items": [
        {
            "id": 1
        },
        {
            "id": 2
        }
    ],
    "nested" [
        {
            "attribute1": "test"
        },
        {
            "attribute1": "test"
        }
    ]

}
2

Our superstar @vsr1 has implemented subqueries over nested data. Ask him or @keshav_m for an early build.

3

Try this.

select d1.*, nested from default d1 use keys ['master'] nest default d2 on keys ARRAY TOSTRING(x.id) for x in d1.items END LET nested = ARRAY {v.attribute1} FOR v in d2 END;

For LEFT NEST, you can use the following options too.

select d1.*, (select d2.attribute1 from default d2 use keys ARRAY TOSTRING(x.id) for x in d1.items END) nested from default d1 use keys ['master'] ;

select d1.*, d3 from default d1 use keys ['master']
LET d3 = (select d2.attribute1 from default d2 use keys ARRAY TOSTRING(x.id) for x in d1.items END);