How to query field inside nested arrays in CosmosDB SQL

Question

How can I return all documents which have parameter.code = "123", given this document structure, using CosmosDB SQL query? Is it necessary to use a UDF? (If so, how?)

{
    "batch_id": "abc",
    "samples": [
        {
            "sample_id": "123",
            "tests": [
                {
                    "parameter": {
                        "code": "123", // <- target
                    }
                }
            ]
        }
    ]
}

Jay Gong Jay Gong · Accepted Answer · 2019-12-06T01:00:32

No need to use UDF(User Defined Function),just use cosmos db query sql with double JOIN.

SQL:

SELECT c.batch_id FROM c
join samples in c.samples 
join tests in samples.tests
where tests.parameter.code = "123"

Output:

How to query field inside nested arrays in CosmosDB SQL

1 Answers