Use ARRAY_CONTAINS with a select statement

Question

I have a query which nearly returns the data I need:

SELECT *
FROM a in c.Things
WHERE ARRAY_CONTAINS(['ThingA', 'ThingB'], a.Name)

The problem is that the array ['ThingA', 'ThingB'] can't be hard-coded, as the values in the array should by dynamically generated based off some query. For this example, that query is this:

select VALUE ARRAY (
  SELECT VALUE a.Name
  FROM a in c.Things
  where a.Visible)
from c
WHERE c.Discriminator='Type'

Which returns something like: ['ThingOne', 'ThingTwo']

Is it possible to include a query inside ARRAY_CONTAINS like this:

SELECT *
FROM a in c.Attributes
WHERE ARRAY_CONTAINS(
  ( select VALUE ARRAY(
    SELECT VALUE a.Name
    FROM a in c.Things
    where a.Visible)
  from c
  WHERE c.Discriminator='Type'
  )
  , a.Name)

If I run this in Cosmos DB Studio I get this error:

Microsoft.Azure.Cosmos.Query.Core.Exceptions.ExpectedQueryPartitionProviderException: {"errors":[{"severity":"Error","location":{"start":147,"end":148},"code":"SC2001","message":"Identifier 'c' could not be resolved."}

Mo B. · Accepted Answer · 2022-09-15 07:29:22Z

2

If I understand correctly, your inner query is independent of the outer query, so what you want is an uncorrelated subquery. This is not supported in Cosmos DB; the documentation says:

Azure Cosmos DB supports only correlated subqueries.

answered Sep 15, 2022 at 7:29

Mo B.

5,9075 gold badges28 silver badges62 bronze badges

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

Collectives™ on Stack Overflow

Use ARRAY_CONTAINS with a select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related