CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
Balaji211
Balaji211

Reputation: 307

Azure cosmos DB correlated subquery not working as expected

The below query is not working in Azure Cosmos DB. It is not fetching any result. Can anyone tell me where and what That I am missing. Trying to get recent item based on timestamp from multiple same sessionId entries.

SELECT c.payload, c.domainname
FROM c JOIN t IN c.domainname
WHERE c.payload.sessionTimestamp = 
(SELECT VALUE MAX(t.payload.sessionTimestamp) FROM t 
WHERE c.payload.sessionId = t.payload.sessionId)

sample JSON structure is as below.

[{
        "domainname": "cardiology",
        "payload": {
            "sessionId": "ABC1234",
            "sessionTimestamp": "2020-02-04T10:14:43.507Z",
            "values": [10, 20, 30, 40, 50]
        }
    },
    {
        "domainname": "cardiology",
        "payload": {
            "sessionId": "ABC1234",
            "sessionTimestamp": "2020-02-05T10:10:43.507Z",
            "values": [60, 70, 80, 90, 100]
        }
    }
]

Upvotes: 1

Views: 4438

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Firstly,your query sql doesn't match your sample data.I suppose that your documents in db as below:

{
        "domainname": "cardiology",
        "payload": {
            "sessionId": "ABC1234",
            "sessionTimestamp": "2020-02-04T10:14:43.507Z",
            "values": [10, 20, 30, 40, 50]
        }
    },
    {
        "domainname": "cardiology",
        "payload": {
            "sessionId": "ABC1234",
            "sessionTimestamp": "2020-02-05T10:10:43.507Z",
            "values": [60, 70, 80, 90, 100]
        }
    }

It seems that you want to implement self-join with cosmos db which is not supported actually.So,i don't think JOIN should be used here.

Trying to get recent item based on timestamp from multiple same sessionId entries.

You need GROUP BY here probably.

sql:

SELECT max(c.payload.sessionTimestamp),c.payload.sessionId from c
group by c.payload.sessionId

result:

enter image description here

Then you would get sessionId array which could be used in next sql. Such as ... where c.sessionId in [sessionId array]

Upvotes: 2

Related Questions