Cosmos DB query for null array like [null] object is present

Question

I need to query from CosmosDB for items that has a null array of data like a [null].

I tried query like this query SELECT TOP 1 c.id from c where ARRAY_CONTAINS(c.data1, [[null]], true). My intent is to query something like this.

SELECT TOP 1 c.id from c where ARRAY_CONTAINS(c.data1, [[null]], true)

NO result is returned for the above query.

var sampledata = `{
  "id": 48259,
  "data": [
    [
      {
        "Elements": [
          {
            "QS": "",
            "TypeC": "C",
            "Id": 378,
            "R": false,
            "KTime": "0",
            "AllKVal": "",
            "KVal": "2",
            "IsGreen": false
          }
        ]
      }
    ],
    [
      null,
      {
        "Elements": [
          {
            "QS": "",
            "TypeC": "CM",
            "Id": 243,
            "R": "",
            "KTime": "0",
            "AllKVal": "",
            "V": true,
            "KVal": "2",
            "IsGreen": false
          }
        ]
      }
    ]
  ],
  "_ts": 1560943024
}`;

How data looks

I think I got the result I am expecting. I removed the additional square bracket and it giving result. I am still testing. SELECT TOP 10 c.id, c.documentrecrodid from c where ARRAY_CONTAINS(c.data,[null],true) ORDER BY c.createdon DESC. I has solved only half the problem only, it gives the pattern [null] but [null], . — RSimple
Not sure what's the meaning of I has solved only half the problem only, it gives the pattern [null] but [null]. Your second sql is for what? Any further sample data? What's you expected result? — Jay Gong
Hi Jay, I have updated the snippet. {...,[null, { "Elements": [......}. This is pattern I am trying to query and not find a easy way. If you see the array has one null value and other has a JSON object. Hope I am making sense. — RSimple

Jay Gong Jay Gong · Accepted Answer · 2019-07-10T06:39:49

I think i get your intention. Let us say, your data maybe like this:

{
    "id": "48259",
    "data": [
        [
            {
                "Elements": [
                    {
                        "QS": "",
                        "TypeC": "C",
                        "Id": 378,
                        "R": false,
                        "KTime": "0",
                        "AllKVal": "",
                        "KVal": "2",
                        "IsGreen": false
                    }
                ]
            }
        ],
        [
            null
        ],
        [
            null,
            {
                "Elements": [
                    {
                        "QS": "",
                        "TypeC": "CM",
                        "Id": 243,
                        "R": "",
                        "KTime": "0",
                        "AllKVal": "",
                        "V": true,
                        "KVal": "2",
                        "IsGreen": false
                    }
                ]
            }
        ]
    ]
}

data array has element is entire null array:

or has element contains null value and other values.

If so,you could use join in your sql to deal with both of them.

SELECT distinct c.id from c 
join data in c.data
where ARRAY_CONTAINS(c.data,[null],true) 
or ARRAY_CONTAINS(data,[null],true)

Output:

Cosmos DB query for null array like [null] object is present

1 Answers