DocumentDB Query documents where Array_Length > 1

Question

I have sets of documents that looks like this:

[
  {
    "Name": "Document1",
    "Properties": {
      "Property1": [
        "Value1",
        "Value2",
        "Value3",
      ]
    },
    "Tags": null
  },
  {
    "Name": "Document2",
    "Properties": {
      "Property1": [
        "Value1",
      ]
    },
    "Tags": null
  },
  {
    "Name": "Document3",
    "Properties": {
      "Property1": [
        "Value1",
        "Value2",
      ]
    "Property2": [
        "Value1",
      ]
    },
    "Tags": null
  }
]

I need to query for any documents where the the Property1 array within the Properties node has more that 1 item. In my example above I would expect to only get back Document1 and Document3. I have spent a great deal of time experimenting with the Array_Contains syntax but keep coming up short. Here is what my latest attempt looks like:

SELECT * FROM Docs d WHERE ARRAY_LENGTH([d.Properties, 'Property1']) > 1

But with my syntax I get back every document.

Answer 1

You need a query like the following:

SELECT * FROM Docs d WHERE ARRAY_LENGTH(d.Properties.Property1) > 1

Note that DocumentDB's grammar works over hierarchical nested data, and you can access properties like d.Properties.Property1, d.Properties.Property1[0], etc. like in a programming language.