question

GaikwadRavindraEXTSGRECOGDVLSW-5537 avatar image
0 Votes"
GaikwadRavindraEXTSGRECOGDVLSW-5537 asked GaikwadRavindraEXTSGRECOGDVLSW-5537 commented

Distinct in Cosmos DB SQL API Returns Inconsistent Response

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.

azure-cosmos-db
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GaikwadRavindraEXTSGRECOGDVLSW-5537, 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 c

Result:

 [
     "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 c


Result:

 [
     {
         "city": "mumbai"
     },
     {
         "city": "hyderabad"
     },
     {}
 ]

Again once we check the documents in your case we would know it better.







0 Votes 0 ·
  • 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.

0 Votes 0 ·
GaikwadRavindraEXTSGRECOGDVLSW-5537 avatar image GaikwadRavindraEXTSGRECOGDVLSW-5537 GaikwadRavindraEXTSGRECOGDVLSW-5537 ·

{
"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
}

0 Votes 0 ·
AnuragSharma-MSFT avatar image AnuragSharma-MSFT GaikwadRavindraEXTSGRECOGDVLSW-5537 ·

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.

0 Votes 0 ·
Show more comments

{
"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
}

0 Votes 0 ·

{
"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
}

0 Votes 0 ·

0 Answers