Finding sub documents recursively within a tree structure

SQL++
1

I am trying to query for sub document that can be anywhere inside a tree like document, based on a value within that sub document.

Consider the following document:

{
  "documentType": "form",
  "name": "Form 1",
  "fields": [
    {
      "label": "group",
      "value": null
      "fields": [
        {
          "label": "age",
          "value": 27
        }
      ]
    },
    {
      "label": "date",
      "value": "24-04-2017"
    },
    {
      "label": "age",
      "value": 25
    }
  ]
}

Within “fields”, each field object can have a “fields” key, that can contain more fields (recursively).

For example, i would like to query for all sub documents that have the label = “age”, resulting into 2 results:

[
  {
    "label": "age",
    "value": 27
  },
  {
    "label": "age",
    "value": 25
  }
]

not sure if it’s possible, but might as well ask :slight_smile:

2

Use ARRAY constructor with WITHIN which will do recursive of all nested objects

ARRAY v FOR v WITHIN fields WHEN v.label = “age” END

INSERT INTO default VALUES ("d01", { "documentType": "form", "name": "Form 1", "fields": [ { "label": "group", "value": null, "fields": [ { "label": "age", "value": 27 } ] }, { "label": "date", "value": "24-04-2017" }, { "label": "age", "value": 25 } ] });
SELECT  ARRAY v FOR v WITHIN  fields WHEN v.label = "age" END FROM default;
1 Like
3

Awesome, thanks @vsr1 !

Now that I have a working example, it leads me to another question: Is is possible to add a Global Secondary Index for this query?
I have been trying to create one, after reading:
https://blog.couchbase.com/making-the-most-of-your-arrays-with-array-indexing/
and
https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
However, I haven’t been able to succesfully create an index for it.

Ideally, I want to be able to run your example query without having a primary index running.

4

when WITHIN used in side array bindings it is not possible