Cosmos DB SQL on nested array without property name

Question

Assume documents of the following schema

{
  "id": 1,
  "children":[
       {"id": "a", "name":"al"},
       {"id": "b", "name":"bob"}
   ]
}

I want to return an array of arrays of all children but filtered on the id property at the root level. Below are the most the known alternatives and limitations:

SELECT * FROM c.children

The above SQL, provides the array of arrays in the right shape but it doens't allow me to filter at the ID in the ROOT level of the document.

SELECT children FROM c WHERE c.id >= 1

The above allows the filtering but returns an array of objects all with the "children" property containing the array.

SELECT child.id, child.name FROM c JOIN child in c.children WHERE c.id >= 1

The above allows the filtering but returns an array of objects. Unlike the previous example the objects are flattened to the child level e.g. property named prefix of "children" is not present.

Again the ordering and grouping children in the returned arrays returned are important on the client side, thus the desired to return all children of a parent grouped in to an array. The first query accomplishes that be doesn't allow filtering.

Steve Johnson · Accepted Answer · 2021-02-14 01:55:01Z

1

Please try this SQL:

SELECT value c.children FROM c WHERE c.id >= 1

Result:

[
    [
        {
            "id": "a",
            "name": "al"
        },
        {
            "id": "b",
            "name": "bob"
        }
    ]
]

answered Feb 14, 2021 at 1:55

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cosmos DB SQL on nested array without property name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related