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 · Accepted Answer · 2019-12-06 01:00:32Z

2

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:

answered Dec 6, 2019 at 1:00

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

How to query field inside nested arrays in CosmosDB SQL

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