Getting duplicate records in select query for the Azure DocumentDB

Question

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.

Can you check the response headers and see if continuation token is returned? — Gaurav Mantri, Feb 10 '17 at 07:44
Try it with escaped double quotes for the second occurrence so instead of `...{"id": '{0}'}...` you'd have `...{"id": \"{0}\"}...`. My working theory is that it's using JSON parsing for that part since you wrap it in curly braces to make it an object and JSON requires double quotes even though SQL works with either double or single. If that fixes it, I'll post as an answer and you can accept it. — Larry Maccherone, Feb 11 '17 at 15:40
it doesn't related to the quotes because other queries are working (which don't have the array ;) ) — Chandresh Khambhayata, Feb 13 '17 at 12:14

score 1 · Accepted Answer · answered Feb 13 '17 at 15:46

1

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.

answered Feb 13 '17 at 15:46

Peter Tirrell

2,962
4
29
52

It's working for me, but not 100% I am getting duplicate records if `c.chi_id = {0} OR t.id = {0}` both are same. – Chandresh Khambhayata Feb 14 '17 at 05:25
I found some solution here ( http://stackoverflow.com/a/33373782/4025613 ) but I don't know how to use this solutions. – Chandresh Khambhayata Feb 14 '17 at 11:29
Yeah, for any sort of aggregate type functionality I've been implementing that in my client code; I'll execute a query like above and then process the results, taking care of duplicates or things like that on my processing end. – Peter Tirrell Feb 14 '17 at 13:59
Please post here once you find the solutions :) – Chandresh Khambhayata Feb 14 '17 at 16:16

Tom Sun - MSFT · Answer 2 · 2017-02-13T12:50:44.220

0

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.

edited Feb 13 '17 at 12:50

answered Feb 13 '17 at 09:40

Tom Sun - MSFT

24,161
3
30
47

I am sorry, but this is not working for me. Let me check the above document link. – Chandresh Khambhayata Feb 13 '17 at 12:08
I have updated the code to `SELECT * FROM TimelineEvent t WHERE ARRAY_CONTAINS ( t.media[0].tagged_chi, { "id":"0af23202-07f9-40a0-90ba-d2e2f6679331" })` in the answer. – Tom Sun - MSFT Feb 13 '17 at 12:51
We could get the multiple code sample from the [official document](https://learn.microsoft.com/en-us/azure/documentdb/documentdb-dotnet-samples) – Tom Sun - MSFT Feb 15 '17 at 09:11
Sorry, I didn't get you and what if I have multiple items in Media? – Chandresh Khambhayata Feb 15 '17 at 11:15

Getting duplicate records in select query for the Azure DocumentDB

2 Answers2