Creating an AND query on a list of items in Azure Cosmos

Question

I'm building an application in Azure Cosmos and I'm having trouble creating a query. Using the dataset below, I want to create a query that only finds CharacterId "Susan" by searching for all characters that have the TraitId of "Athletic" and "Slim".

Here is my JSON data set

    {
        "characterId": "Bob",
        "traits": [
            {
                "traitId": "Athletic",
                "traitId": "Overweight"
            }
        ],
    },
    {
        "characterId": "Susan",
        "traits": [
            {
                "traitId": "Athletic",
                "traitId": "Slim"
            }
        ],
    },
    {
        "characterId": "Jerry",
        "traits": [
            {
                "traitId": "Slim",
                "traitId": "Strong"
            }
        ],
    }
]

The closest I've come is this query but it acts as an OR statement and what I want is an AND statement.

SELECT * FROM Characters f WHERE f.traits IN ("Athletic", "Slim")

Any help is greatly appreciated.

EDITED: I figured out the answer to this question. If anyone is interested this query gives the results I was looking for:

SELECT * FROM Characters f 
WHERE EXISTS (SELECT VALUE t FROM t IN f.traits WHERE t.traitId = 'Athletic')
    AND EXISTS (SELECT VALUE t FROM t IN f.traits WHERE t.traitId = 'Slim')

Hi, thanks for your sharing and you could summarize your solution as an complete answer to end this answer for anyone's reference on the forum.Thanks. — Jay Gong

Christian King Christian King · Accepted Answer · 2019-09-17T13:09:11

The answer that worked for me is to use EXISTS statements with SELECT statements that searched the traits list. In my program I can use StringBuilder to create a SQL statement that concatenates an AND EXISTS statement for each of the traits I want to find:

SELECT * FROM Characters f 
WHERE EXISTS (SELECT VALUE t FROM t IN f.traits WHERE t.traitId = 'Athletic')
    AND EXISTS (SELECT VALUE t FROM t IN f.traits WHERE t.traitId = 'Slim')

Creating an AND query on a list of items in Azure Cosmos

1 Answers