26

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.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
INNVTV
  • 3,155
  • 7
  • 37
  • 71

1 Answers1

39

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.

Aravind Krishna R.
  • 7,885
  • 27
  • 37
  • 1
    Thanks Aravind. This works on properties without spaces in the names, but breaks down on queries where "Property1" might be spelled "Property 1" - How can I use a string for that variable. So far I've tried: SELECT * FROM Docs d WHERE ARRAY_LENGTH(d.Properties.["Property 1"]) > 1 – INNVTV Aug 11 '16 at 05:18
  • 2
    I figured it out: SELECT * FROM Docs d WHERE ARRAY_LENGTH(d.Properties["Property 1"]) > 1 – INNVTV Aug 11 '16 at 05:24
  • `SELECT * FROM myItems AS m WHERE ARRAY_LENGTH(m.subCollection) > 0` I don't know why but this query returns an empty response `[]`. There are items with non-empty `subCollection` so I'm expecting to get them. – savbace Dec 14 '20 at 16:08