Azure CosmosDB SQL query on nested Array

Question

We have a below document structure.

{
"id":"GUID",
    "customer": {
            "contacts": [
                {
                    "type": "MOBILE",
                    "status": "CONFIRMED",
                    "value": "xxxx"
                },
                {
                    "type": "EMAIL",
                    "status": "CONFIRMED",
                    "value": "aaaa"
                }
            ],
            "addresses": [
                {

                    "country": "xxx"
                }
            ]
        }
}

and need to search for customer->contacts where value="aaaa". I tried with below options

1)  SELECT c.id FROM c
    join customer in c.customer
    join contacts in c.customer.contacts
    where contacts.value = "aaaa"

2) SELECT c.id FROM c WHERE c.customer.contacts[0].value= "aaaa"

Getting Syntax error 400 bad request Any help highly appreciated

Alex · Accepted Answer · 2019-12-07 22:06:27Z

1

Part of your issue is that value cannot be searched as easily as other properties. Here are possible solutions:

SELECT c.id FROM c
join contacts in c.customer.contacts where contacts["value"] = "aaaa"

SELECT c.id FROM c WHERE c.customer.contacts[1]["value"] = "aaaa"

Document DB SQL Api - unable to query json property with name 'value' and its value is integer

edited Dec 7, 2019 at 22:06

answered Dec 7, 2019 at 11:51

Alex

18.6k3 gold badges49 silver badges95 bronze badges

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

1 Comment

Manjunath Over a year ago

Thanks @AlexAIT, your query worked as charm. I was not aware of limitation, JSON property name - 'value'.

Collectives™ on Stack Overflow

Azure CosmosDB SQL query on nested Array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related