Compare array properties in Cosmos DB (SQL API)

Question

Suppose we have Cosmos DB documents of the following form:

{ "propA": [1, 2, 3], 
  "propB": [7, 2, 4, 2] }

Is it possible to write a query that returns all documents where at least one element in propA is a member of propB (in order words, where the two properties have a non-empty intersection)?

And if yes, is it also possible using LINQ (and the .NET Cosmos Client)?

Not sure on the query itself, but assuming you control the writing of the data, I'd try the route of computing this intersection fact and adding it as a boolean value on the document before writing. No problem querying for "intersects = true" :) — Noah Stahl

Steve Zhao Steve Zhao · Accepted Answer · 2020-08-07T07:25:55

Please try something like this sql:

SELECT distinct value c FROM c join d in c.propA where array_contains(c.propB,d)

Here is my result:

LINQ:

FeedIterator<Prop> setIterator = this.container.GetItemLinqQueryable<Prop>().SelectMany(prop => prop.propA.Where(e => prop.propB.Contains(e)).Select(e => prop).Distinct()).ToFeedIterator<Prop>();

Hope this can help you.

Compare array properties in Cosmos DB (SQL API)

1 Answers