Cosmos DB - can't use a property (array) with IN Keyword

Question

I'm attempting to query for several documents at once, using some properties that were found on the first document, similar to a TSQL left join on property value.

My attempt in CosmosDB:

select c from assets
join ver on c.versions
where c.id = '123' OR c.id IN ver.otherIds   
--NOTE: ver.otherIds is an array

The query above results in a syntax error, stating it doesn't understand ver.otherIds. The docs state the syntax to be where c.id in ("123","456"...)

Things I've tried to work around this:

Attempted Custom UDF that takes in the array generates the syntax wanted Ex) ["123,"456"] --> "("123", "456")
Attempted using array_contains(ver.otherIds, c.id)
Attempted sub query approach, which produced a "The cardinality of a scalar subquery result set cannot be greater than one" error:

select value c from c
where array_contains((select ... that produces array), c.id)

None of the above worked.

I can, of course, pull the first asset, then generate a second query to pull the rest, but I'd rather not do that. I can also just de-normalize all the data, but without giving specifics to my scenario, it would end up being a very bad idea.

Any ideas?

Thanks in advance!

Hi,does my answer helps you?

Jay Gong
– Jay Gong

2019-04-10 02:45:52 +00:00
Commented Apr 10, 2019 at 2:45 — Jay Gong
– Jay Gong, Commented Apr 10, 2019 at 2:45

Jay Gong · Accepted Answer · 2019-04-01 02:49:46Z

1

You could use your second scenario:ARRAY_CONTAINS.

My sample document:

[
    {
        "id": "1",
        "versions": [
            {
                "otherIds": [
                    "1",
                    "2",
                    "3"
                ]
            }
        ]
    },
    {
        "id": "2",
        "versions": [
            {
                "otherIds": [
                    "1",
                    "2",
                    "3"
                ]
            },
            {
                "otherIds": [
                    "123",
                    "2",
                    "3"
                ]
            }
        ]
    },
    {
        "id": "123",
        "versions": [
            {
                "otherIds": [
                    "1",
                    "2",
                    "3"
                ]
            },
            {
                "otherIds": [
                    "123",
                    "2",
                    "3"
                ]
            }
        ]
    }
]

SQL：

SELECT distinct c.id,c.versions FROM c
join ver in  c.versions
where c.id="123" or array_contains(ver.otherIds,c.id,false)

ARRAY_CONTAINS function can specify if the match is full or partial.

answered Apr 1, 2019 at 2:49

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

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

1 Comment

Porschiey Over a year ago

Hi Jay, Unfortunately this answer is something I've already tried - it doesn't work. The answer I came up with is to move the link down to the child object instead of storing an array in the parent. Then i can search for c.id ='foo' or c.parentId = 'foo'

Collectives™ on Stack Overflow

Cosmos DB - can't use a property (array) with IN Keyword

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related