Query on Cosmos SQL

Question

I have read the Azure cosmos document and it currently says that it doesn't allow to JOIN 2 different collection in the same DB. I need help on achieving the below scenario. Below is the documents in Cosmos DB. I am using SQL API for the same.

All the below documents are in same collection. 'id' is the partition key.

Document 1:

{
    "id": "12343920",
    "status": "1",
    "code": "111116"
}

Document 2:

{
    "id": "12343921",
    "status": "8",
    "code": "111117"
}

Document 3: [ Different from other documents ]

{
    "id": "active",
    "Part": [
        {
            "Name": "ABC",
            "Status": [
                "1",
                "2",
                "3"
            ]
        },
        {
            "Name": "DEF",
            "Status": [
                "6",
                "2",
                "8"
            ]
        }
    ],
}

I have a stored procedure where the API comes with 3 parameters,

id
code
Name

I need to find the status from Document 3 for the corresponding Name [ from API ] and then need to apply IN clause in the other documents to find the matching response.

I tried few queries as below.

I tried to get the array of string for the status from document 3:

SELECT Value t.Status
from f
JOIN t in f.Part
where t.Name=@Name

This query works in SQL editor[ if I substitute @Name with ABC] but shows no document when executing in stored procedure.

Other code :

SELECT n.id,n.code,n.Status
FROM order n
where n.Code=@Code
and n.id=@id and n.Status IN (
 SELECT ARRAY(
  SELECT Part.Status
  FROM Part
  in order.Part
  WHERE Part.Name=@Name)
 FROM orders WHERE orders.id='active')

It says error near SELECT statement.

Result what I am expecting is as below which matches the parameters from the API.

{
    "id": "12343920",
    "status": "1",
    "code": "111116"
}

Hi Jay,Thanks.I am still working this out. I am trying this with Cosmos SQL editor. Not sure if that is the right option to test. — Sathya
ok,i tried on my side but it can't be searched by a single query. — Jay Gong
Thanks Jay, I need to incorporate this in the Cosmos Stored proc. — Sathya
Yes,please incorporate the below query sqls in the stored procedure,it could be implemented. — Jay Gong
Hi,if my answer helps you,you could mark it for others' reference.Thanks a lot. — Jay Gong

Jay Gong Jay Gong · Accepted Answer · 2019-04-03T06:10:11

According to this case,cosmos db does't support sub query so far,so it says error near SELECT statement.

To achieve your requirements,i suggest you using double sql query.

first sql to select the Status array filter conditions.

SELECT value t.Status from f JOIN t in f.Part where t.Name= @Name

second sql to match the status with filter conditions.

SELECT f.id,f.code,f.status from f where IS_DEFINED (f.Part) = false 
and ARRAY_CONTAINS(@STATUS,f.status,false)

Query on Cosmos SQL

1 Answers