Query Cosmos DB for the same objects from multiple schemas

Question

Give the following documents stored in Cosmos DB, how do I go about getting all of the Child/Children elements where the FirstName field of each child is "Bob"? I'm trying to use the SQL query syntax, but have not found the right way to do this that combines both document schema results.

// Document 1
{
    "id": "document1",
    "Child": {
        "FirstName": "Bob",
        "LastName": "Smith"
    }
}

// Document 2
{
    "id": "document2",
    "Children": [
        {
            "Name": "Bob",
            "LastName": "Jones"
        },
        {
            "Name": "Sue",
            "LastName": "Jones"
        }
    ]
}

I'm trying to write a query that looks for all "Bob" child elements to achieve the following output:

[
    {
        "FirstName": "Bob",
        "LastName": "Smith"
    },
    {
        "Name": "Bob",
        "LastName": "Jones"
    },
]

It's more that some documents have a single "Child" element and other documents have that element in an array. In this case the "Child" refers to the common structure rather than the field name. — Brian Vallelunga

Jay Gong Jay Gong · Accepted Answer · 2019-10-08T06:03:34

Cosmos db documents are stored as json format, you can't treat the Child property(Superior structure) and Children property(Sub structure) equally with a single sql query.

Then can't be flatten and put into one object,please see the example:

The c.Child does not display. So,i'm afraid you need to query Child and Children separately, then merge them for your requirements.

I tried to explain here. In one single query sql is not possible. For example, document 1 does not includes Children Array,document 2 does. In one single sql, C JOIN Children is necessary. But for document 1, Child join nothing is nothing so that no results will be pulled out. You could try it.

Since UNION feature is not supported by cosmos db, i still suggest following above suggestion to query them separately and merge.

Query Cosmos DB for the same objects from multiple schemas

1 Answers