Can Azure Cosmos DB do this kind of query?

Question

I have a JSON object stored in Azure Cosmos DB, and I'm seeing if there's a way to write workable queries doing basic things like Order By.

The structure looks something like :

[ 
   { 
      "id":"id1",
      "title":"test title",
      "dataRecord":{ 
         "version":1,
         "dataRecordItems":[ 
            { 
               "itemTitle":"item title 1",
               "type":"string",
               "value":"My First Title"
            },
            { 
               "itemTitle":"item number",
               "type":"number",
               "value":1
            },
            { 
               "itemTitle":"date",
               "type":"date",
               "value":"21/11/2019 00:00:00"
            }
         ]
      }
   },
   { 
      "id":"id2",
      "title":"test title again",
      "dataRecord":{ 
         "version":1,
         "dataRecordItems":[ 
            { 
               "itemTitle":"item title 2",
               "type":"string",
               "value":"My Second Title"
            },
            { 
               "itemTitle":"item number",
               "type":"number",
               "value":2
            },
            { 
               "itemTitle":"date",
               "type":"date",
               "value":"20/11/2019 00:00:00"
            }
         ]
      }
   ]

I can use ARRAY_CONTAINS to find objects with a particular value, but I run into all kinds of issues if I try to sort by an the value of an object which has the title of "date".

So, as an example, I'd like to be able to say something like (pseudoish code here):

SELECT  * FROM c WHERE
    ARRAY_CONTAINS(c.dataRecord.dataRecordItems, 
    {"itemTitle":"item title 2", "value" : "My Second Title"}, true)
    AND
    ARRAY_CONTAINS(c.dataRecord.dataRecordItems,{"itemTitle":"item number", "value" : 2}, true)
    ORDER BY < *** SOMEHOW GET THE DATE HERE from itemTitle = date ***

Then, in this simple case, I would everything returned, but ordered by date.

Obviously in the future I would be pulling out individual fields, but it's all kind of moot if I can't do the first part.

Just wondering if anyone has any great ideas.

Cheers!

Hi,could you please post your current sql and your desired output? — Jay Gong, Oct 21 '19 at 05:34
Thanks for looking. I've updated the question to try to make it more clear. — Aibynn, Oct 21 '19 at 08:27

score 0 · Answer 1 · answered Oct 21 '19 at 01:20

You need to store the date in ISO 8601 format:

Year:
      YYYY (eg 1997)
   Year and month:
      YYYY-MM (eg 1997-07)
   Complete date:
      YYYY-MM-DD (eg 1997-07-16)
   Complete date plus hours and minutes:
      YYYY-MM-DDThh:mmTZD (eg 1997-07-16T19:20+01:00)
   Complete date plus hours, minutes and seconds:
      YYYY-MM-DDThh:mm:ssTZD (eg 1997-07-16T19:20:30+01:00)
   Complete date plus hours, minutes, seconds and a decimal fraction of a
second
      YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)
where:

     YYYY = four-digit year
     MM   = two-digit month (01=January, etc.)
     DD   = two-digit day of month (01 through 31)
     hh   = two digits of hour (00 through 23) (am/pm NOT allowed)
     mm   = two digits of minute (00 through 59)
     ss   = two digits of second (00 through 59)
     s    = one or more digits representing a decimal fraction of a second
     TZD  = time zone designator (Z or +hh:mm or -hh:mm)

https://www.w3.org/TR/NOTE-datetime

Can Azure Cosmos DB do this kind of query?

1 Answers1