Is it possible to rewrite this CosmosDb SQL query with SubQuery instead of self-join?

Question

I've got a collection of documents in CosmosDb and I want to perform an SQL query against a nested collection of those documents but I'm only querying data on one specific document at a time.

Example document

{
  "id": "d0769c6e-2334-4bb7-ae41-e45a8ac8896c",
  "Scores": [
    {
      "Name": "blabla",
      "Score": 10
    },
    {
      "Name": "blabla",
      "Score": 30
    }
  ]
}

Currently I have the following query that works :

SELECT SUM(item.Score) 
FROM c
JOIN item IN c.Scores
WHERE c.id = "d0769c6e-2334-4bb7-ae41-e45a8ac8896c" AND item.Name = 'blabla'

I'd rather write a query like this :

SELECT SUM(row.Score)
FROM row IN (SELECT c.Scores FROM c WHERE c.id = "d0769c6e-2334-4bb7-ae41-e45a8ac8896c")
WHERE row.Name = 'blabla'

But of course it does not work. Do you know of any possible manner of querying the second way ?

Thanks !!

Peppi Alexandrova Peppi Alexandrova · Accepted Answer · 2019-11-19T08:38:11

You can try with a similar one if this works for you:

SELECT SUM(item.Score)
FROM c
JOIN (SELECT s.Score, s.Name FROM s IN c.Scores ) as item
WHERE item.Name='blabla' 
and c.id='d0769c6e-2334-4bb7-ae41-e45a8ac8896c'

Is it possible to rewrite this CosmosDb SQL query with SubQuery instead of self-join?

1 Answers