CosmosDB SQL query that matches multiple values in an array

Question

I am working with Cosmos DB and I want to write a SQL query that will match multiple values in an array. To elaborate, imagine you have the following collection:

[
    {
        "id": "31d4c08b-ee59-4ede-b801-3cacaea38808",
        "name": "Oliver Queen",
        "occupations": [
            {
                "job_title": "Billionaire",
                "job_satisfaction": "pretty good"
            },
            {
                "job_title": "Green Arrow",
                "job_satisfaction": "meh"
            }
        ]
    },
    {
        "id": "689bdc38-9849-4a11-b856-53f8628b76c9",
        "name": "Bruce Wayne",
        "occupations": [
            {
                "job_title": "Billionaire",
                "job_satisfaction": "pretty good"
            },
            {
                "job_title": "Batman",
                "job_satisfaction": "I'm Batman"
            }
        ]
    },
    {
        "id": "d1d3609a-0067-47e4-b7ff-afc7ee1a0147",
        "name": "Clarke Kent",
        "occupations": [
            {
                "job_title": "Reporter",
                "job_satisfaction": "average"
            },
            {
                "job_title": "Superman",
                "job_satisfaction": "not as good as Batman"
            }
        ]
    }
]

I want to write a query that will return all entries that have an occupation with the job_title of "Billionaire" and "Batman". Just to be clear the results must have BOTH job_titles. So in the above collection it should only return Bruce Wayne.

So far I have tried:

SELECT c.id, c.name, c.occupations FROM c
WHERE ARRAY_CONTAINS(c.occupations, {'job_title': 'Billionaire' })
AND ARRAY_CONTAINS(c.occupations, {'job_title': 'Batman' })

and

SELECT c.id, c.name, c.occupations FROM c
WHERE c.occupations.job_title = 'Batman' 
AND c.occupations.job_title = 'Billionaire'

Both of which returned empty results.

Thanks in advance

Answer 1

You need to use ARRAY_CONTAINS(array, search_value, is_partial_match = true), i.e. the query is:

SELECT c.id, c.name, c.occupations 
FROM c
WHERE ARRAY_CONTAINS(c.occupations, {'job_title': 'Billionaire' }, true)
AND ARRAY_CONTAINS(c.occupations, {'job_title': 'Batman' }, true)