CODEWITHSUNDEEP
azure-cosmosdb
Arnab
Arnab

Reputation: 2354

subquery not working in documentdb

My following query is working..

SELECT d.pub_user.userid FROM d where d.pri_data.user.email="[email protected]"

This gives me result as below..

[
  {
    "userid": "1e4491ef27097262"
  },.....
]

The following query is also working..

SELECT * FROM c WHERE c.pub_user.userid  IN ("1e4491ef27097262")

But, when I try the following, it does not work..

SELECT * FROM c WHERE c.pub_user.userid  IN (SELECT d.pub_user.userid FROM d where d.pri_data.user.email="[email protected]")

It gives an error.. Syntax error, incorrect syntax near 'SELECT'

Any help is sincerely appreciated.

Thanks

Upvotes: 1

Views: 1011

Answers (1)

hsulriksen
hsulriksen

Reputation: 592

subquery is currently not supported. As per this answer, you can try to leverage a stored procedure.

When one start hitting joins or subquery is an early indicator that your data document structure is not optimal. I am however wondering why you cannot query by email without subquery in your final example. Like this

SELECT * FROM c WHERE c.pri_data.user.email="[email protected]"

If this is not possible because these data are in separate documents perhaps email could be added to the docments.

Upvotes: 4

Related Questions