cosmos db query self join with null array

问题:

I want to get records from cosmos db azure. there are two conditions as below-

SELECT p.name, p.id
FROM Product p join grp in p.groups
where grp.name = 'abc' or ARRAY_LENGTH(grp) = 0 

The first part is giving me result without or condition i.e.(document 1). But i want those records also which does not contain any record in p.groups i.e.(document 2).

My Data:

Document 1-

"id": "123",
"name": "Test Name",
"groups": [
        {
            "name": "abc"        }
]

Document 2-

"id": "1234",
"name": "Test Name Blank",
"groups": []

Expected Output:

[
    {
        "name": "Test Name",
        "id": "123"
    },
{
        "name": "Test Name Blank",
        "id": "1234"
    },
]

回答1:

You could use following sql :

SELECT c.id,c.name from c where ARRAY_LENGTH(c.groups) =0 or ARRAY_CONTAINS(c.groups, {"name": "abc"}, true)

Output result:

Hope it helps you.