Use ARRAY_CONTAINS with a select statement

Viewed 40

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."}

1 Answers

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.

Related