Azure Cosmos DB
An Azure NoSQL database service for app development.
1,052 questions
Distinct in Cosmos DB SQL API Returns Inconsistent Response
Gaikwad, Ravindra (EXT) (SGRE COG DVL SW)
6
Reputation points
When distinct query is executed against Cosmos DB SQL API, it gives inconsistent response.
e.g. Select distinct c.field from c
Inconsistent means:
- no of records are different.
- not all distinct values are returned.
{count} vote
-
Anurag Sharma 17,381 Reputation points
2022-04-06T14:20:11.677+00:00 Hi @Gaikwad, Ravindra (EXT) (SGRE COG DVL SW) , welcome to Microsoft Q&A forum.
As we understand, while you are running the distinct query you are not getting the desired distinct results. It would be easier for us if you could provide couple of documents and the screenshot when you run the query.
However, distinct keyword would work in 2 ways, one with value and one without value. As an example I created 3 documents in a container as below:
{ "id": "1", "city": "mumbai" } { "id": "2", "city": "hyderabad" } { "id": "3" }Now if I run below query with value keyword, it will just give the distinct values present in the container.
SELECT distinct value c.city FROM cResult:
[ "mumbai", "hyderabad" ]However if I run the distinct query without value keyword then it will return the unique objects even if city is not present there as below:
SELECT distinct c.city FROM cResult:
[ { "city": "mumbai" }, { "city": "hyderabad" }, {} ]Again once we check the documents in your case we would know it better.
-
Gaikwad, Ravindra (EXT) (SGRE COG DVL SW) 6 Reputation points
2022-04-07T07:02:20.84+00:00 - I am using distinct without value, because I need to perform distinct on multiple fields also.
- My collection contains around 72000 documents.
In some cases, when I use filters along with distinct, I get all documents.
But distinct without filter does not give all documents. -
Gaikwad, Ravindra (EXT) (SGRE COG DVL SW) 6 Reputation points
2022-04-07T07:02:40.4+00:00 {
"a": "a",
"b": 106.0,
"c": 10.0,
"d": "GD181875.R3",
"h": [
72.0,
80.0,
90.0,
93.0,
120.0,
123.0,
125.0
],
"p": 2500.0,
"n": 105.5,
"cd": "GD207119.R0",
"no": {
"no1": 0.0,
"no2": 0.0,
"no3": 0.0,
"no4": 0.0,
"no5": 0.0,
"no6": 0.0,
"no7": 0.0,
"no8": 0.0
},
"wd": 0.94,
"cc": 0.881,
"vc": 3.0,
"pc": 5.0,
"vp": 3.0,
"dt": "model",
"time": "2021-10-29 07:59:01.910863",
"dataVersion": "v1",
"isLatest": true,
"versionUpdateDate": "29-10-2021",
"_partition_key": "v1",
"id": "2402b58f-53dc-40fa-99f1-91f012d6018e",
"_rid": "ODVsAN5RaDIBAAAAAAAAAA==",
"_self": "dbs/ODVsAA==/colls/ODVsAN5RaDI=/docs/ODVsAN5RaDIBAAAAAAAAAA==/",
"_etag": "\"100018b9-0000-0d00-0000-61c31b960000\"",
"_attachments": "attachments/",
"revisionNumber": "r1",
"isDeleted": false,
"_ts": 1640176534
} -
Gaikwad, Ravindra (EXT) (SGRE COG DVL SW) 6 Reputation points
2022-04-07T07:06:38.597+00:00 {
"a": "a",
"b": 106.0,
"c": 10.0,
"d": "GD181875.R3",
"h": [
72.0,
80.0,
90.0,
93.0,
120.0,
123.0,
125.0
],
"p": 2500.0,
"n": 105.5,
"cd": "GD207119.R0",
"no": {
"no1": 0.0,
"no2": 0.0,
"no3": 0.0,
"no4": 0.0,
"no5": 0.0,
"no6": 0.0,
"no7": 0.0,
"no8": 0.0
},
"wd": 0.94,
"cc": 0.881,
"vc": 3.0,
"pc": 5.0,
"vp": 3.0,
"dt": "model",
"time": "2021-10-29 07:59:01.910863",
"dataVersion": "v1",
"isLatest": true,
"versionUpdateDate": "29-10-2021",
"_partition_key": "v1",
"id": "2402b58f-53dc-40fa-99f1-91f012d6018e",
"_rid": "ODVsAN5RaDIBAAAAAAAAAA==",
"_self": "dbs/ODVsAA==/colls/ODVsAN5RaDI=/docs/ODVsAN5RaDIBAAAAAAAAAA==/",
"_etag": "\"100018b9-0000-0d00-0000-61c31b960000\"",
"_attachments": "attachments/",
"revisionNumber": "r1",
"isDeleted": false,
"_ts": 1640176534
} -
Gaikwad, Ravindra (EXT) (SGRE COG DVL SW) 6 Reputation points
2022-04-07T07:09:49.137+00:00 {
"a": "a",
"b": 106.0,
"c": 10.0,
"d": "GD181875.R3",
"h": [
72.0,
80.0,
90.0,
93.0,
120.0,
123.0,
125.0
],
"p": 2500.0,
"n": 105.5,
"cd": "GD207119.R0",
"no": {
"no1": 0.0,
"no2": 0.0,
"no3": 0.0,
"no4": 0.0,
"no5": 0.0,
"no6": 0.0,
"no7": 0.0,
"no8": 0.0
},
"wd": 0.94,
"cc": 0.881,
"vc": 3.0,
"pc": 5.0,
"vp": 3.0,
"dt": "model",
"time": "2021-10-29 07:59:01.910863",
"dataVersion": "v1",
"isLatest": true,
"versionUpdateDate": "29-10-2021",
"_partition_key": "v1",
"id": "2402b58f-53dc-40fa-99f1-91f012d6018e",
"_rid": "ODVsAN5RaDIBAAAAAAAAAA==",
"_self": "dbs/ODVsAA==/colls/ODVsAN5RaDI=/docs/ODVsAN5RaDIBAAAAAAAAAA==/",
"_etag": "\"100018b9-0000-0d00-0000-61c31b960000\"",
"_attachments": "attachments/",
"revisionNumber": "r1",
"isDeleted": false,
"_ts": 1640176534
} -
Anurag Sharma 17,381 Reputation points
2022-04-07T07:28:38.277+00:00 Thanks for responding back.
So without filter you are seeing less number of documents with distinct query.
Are you using any SDK to run these queries or running through Data Explorer only? And what is the RU you configured for this container? Based on these details I will try to replicate this and get back.
-
Gaikwad, Ravindra (EXT) (SGRE COG DVL SW) 6 Reputation points
2022-04-07T07:29:57.153+00:00 .Net SDK v3 and 400 RUs
{"a":"a","b":106,"c":10,"d":"GD181875.R3","h":[72,80,90,93,120,123,125],"p":2500,"n":105.5,"cd":"GD207119.R0","no":{"no1":0,"no2":0,"no3":0,"no4":0,"no5":0,"no6":0,"no7":0,"no8":0},"wd":0.94,"cc":0.881,"vc":3,"pc":5,"vp":3,"dt":"model","time":"2021-10-29 07:59:01.910863","dataVersion":"v1","isLatest":true,"versionUpdateDate":"29-10-2021","_partition_key":"v1","id":"2402b58f-53dc-40fa-99f1-91f012d6018e","_rid":"ODVsAN5RaDIBAAAAAAAAAA==","_self":"dbs/ODVsAA==/colls/ODVsAN5RaDI=/docs/ODVsAN5RaDIBAAAAAAAAAA==/","_etag":"\"100018b9-0000-0d00-0000-61c31b960000\"","_attachments":"attachments/","revisionNumber":"r1","isDeleted":false,"_ts":1640176534}
-
Gaikwad, Ravindra (EXT) (SGRE COG DVL SW) 6 Reputation points
2022-04-07T07:35:39.073+00:00 400 RUs
{
"a": "a",
"b": 106.0,
"c": 10.0,
"d": "GD181875.R3",
"h": [
72.0,
80.0,
90.0,
93.0,
120.0,
123.0,
125.0
],
"p": 2500.0,
"n": 105.5,
"cd": "GD207119.R0",
"no": {
"no1": 0.0,
"no2": 0.0,
"no3": 0.0,
"no4": 0.0,
"no5": 0.0,
"no6": 0.0,
"no7": 0.0,
"no8": 0.0
},
"wd": 0.94,
"cc": 0.881,
"vc": 3.0,
"pc": 5.0,
"vp": 3.0,
"dt": "model",
"time": "2021-10-29 07:59:01.910863",
"dataVersion": "v1",
"isLatest": true,
"versionUpdateDate": "29-10-2021",
"_partition_key": "v1",
"id": "2402b58f-53dc-40fa-99f1-91f012d6018e",
"_rid": "ODVsAN5RaDIBAAAAAAAAAA==",
"_self": "dbs/ODVsAA==/colls/ODVsAN5RaDI=/docs/ODVsAN5RaDIBAAAAAAAAAA==/",
"_etag": "\"100018b9-0000-0d00-0000-61c31b960000\"",
"_attachments": "attachments/",
"revisionNumber": "r1",
"isDeleted": false,
"_ts": 1640176534
}
Sign in to comment