CODEWITHSUNDEEP
azureselectazure-cosmosdbnosql
Chandresh Khambhayata
Chandresh Khambhayata

Reputation: 1777

Getting duplicate records in select query for the Azure DocumentDB

I need to write a Select query for the below JSON data in Azure DatabaseDB.

{
  "Result": [
    {
      "media": [
        {
          "url": "https://someurl.com",
          "thumb_url": "https://someurl.com",
          "id": "f545f874-a9b4-4573-a0b0-b2d50a7994e0",
          "removed": false,
          "size": 133454,
          "length": 0,
          "type": "IMG",
          "avail": true,
          "has_thumb": true,
          "tagged_chi": [
            {
              "chi_id": "1069b9ef-1028-45f4-b9a1-a40e0d438f4e",
              "tag_x": 262.048,
              "tag_y": 157.472,
              "tag_by": "d481a522-6e2f-4dc6-8aeb-bc87cf27287d",
              "created": 1486723018,
              "last_updated": 1486723018
            },
            {
              "chi_id": "7102fc10-62e8-4d0a-9fcf-35645253fcef",
              "tag_x": 231.648,
              "tag_y": 146.528,
              "tag_by": "d481a522-6e2f-4dc6-8aeb-bc87cf27287d",
              "created": 1486723018,
              "last_updated": 1486723018
            }
          ],
          "created": 1486723012,
          "last_updated": 1486723017
        }
      ],
      "id": "23bcd070-0f64-4914-8bc1-d5e936552295",
      "acc_id": "d481a522-6e2f-4dc6-8aeb-bc87cf27287d",
      "chi_id": "7102fc10-62e8-4d0a-9fcf-35645253fcef",
      "is_note": false,
      "title": "",
      "when": -2147483648,
      "loc_id": null,
      "col_id": null,
      "comment": null,
      "removed": false,
      "created": -2147483648,
      "last_updated": -2147483648,
      "note_type": null,
      "note_value": null
    },
    {
      "media": [
        {
          "url": "https://someurl.com",
          "thumb_url": "https://someurl.com",
          "id": "7665b921-2790-496b-a70f-30afae43d8c6",
          "removed": false,
          "size": 6872977,
          "length": 0,
          "type": "IMG",
          "avail": true,
          "has_thumb": true,
          "tagged_chi": [
            {
              "chi_id": "1069b9ef-1028-45f4-b9a1-a40e0d438f4e",
              "tag_x": 2305.152,
              "tag_y": 686.5653,
              "tag_by": "d481a522-6e2f-4dc6-8aeb-bc87cf27287d",
              "created": 1486976119,
              "last_updated": 1486976119
            },
            {
              "chi_id": "7102fc10-62e8-4d0a-9fcf-35645253fcef",
              "tag_x": 1070.757,
              "tag_y": 1038.741,
              "tag_by": "d481a522-6e2f-4dc6-8aeb-bc87cf27287d",
              "created": 1486976119,
              "last_updated": 1486976119
            }
          ],
          "created": 1486976100,
          "last_updated": 1486976118
        }
      ],
      "id": "58fa3c58-5508-4371-83f4-405332c636e1",
      "acc_id": "d481a522-6e2f-4dc6-8aeb-bc87cf27287d",
      "chi_id": "7102fc10-62e8-4d0a-9fcf-35645253fcef",
      "is_note": false,
      "title": "",
      "when": -2147483648,
      "loc_id": null,
      "col_id": null,
      "comment": null,
      "removed": false,
      "created": -2147483648,
      "last_updated": -2147483648,
      "note_type": null,
      "note_value": null
    }
  ],
  "Continuation": null
}

I was trying something like below but it is not working for me. I want the data matched to Media => tagged_chil => id

Query suggested by, @peter-tirrell:

string.Format("select c.id, c.acc_id, c.chi_id, c.is_note, c.title, c.loc_id, c.media, t from c JOIN m IN c.media JOIN t IN m.tagged_chi where c.chi_id = '{0}' OR t.chi_id = '{0}'", childId)

Minor changes in @peter-tirrell's query:

string.Format("select c.id, c.acc_id, c.chi_id, c.is_note, c.title, c.loc_id, c.media, t from c JOIN m IN c.media JOIN t IN m.tagged_chi where c.chi_id = '{0}' OR ( t.chi_id != c.chi_id AND t.chi_id = '{0}')", childId)

I am getting duplicate records if the c.child and t.child both are having same values.

Upvotes: 1

Views: 2559

Answers (2)

Peter Tirrell
Peter Tirrell

Reputation: 3003

You could potentially use JOINs to flatten the structure which might help with querying, too. Something like:

select 
c.id,
c.acc_id,
c.chi_id,
c.is_note,
c.title,
c.loc_id,
m,
t
from c JOIN m IN c.media
JOIN t IN m.tagged_chi
where c.chi_id = {0} OR t.id = {0}

Then you can select out whichever specific data fields you need.

Upvotes: 1

Tom Sun
Tom Sun

Reputation: 24529

Base on my experience, your query code will return null. Because ARRAY_CONTAINS it will return a Boolean indicating whether the array contains the specified value. That means your query code can be short as SELECT * FROM TimelineEvent t WHERE OR ARRAY_CONTAINS ( t.media, true) that will return null in your case.

Please have a try to use the following code:

SELECT * FROM TimelineEvent t WHERE  ARRAY_CONTAINS ( t.media[0].tagged_chi, {  
               "id":"0af23202-07f9-40a0-90ba-d2e2f6679331"
             })

We also could use UDFs to implement it with customized code, more detail about UDF,please refer to document.

Upvotes: 0

Related Questions