N1QL query to select a dynamic key in Couchbae

Question

I have the following structure, where the key for the array is dynamic and equal with the value of the key "batch".

[
  {
    "7163": [
      {
        "name": "license",
        "ext": "jpg",
      }
    ],
    "batch": "7163",
    "type": "ECS"
  },
  {
    "8012": [
      {
        "name": "jan-statement",
        "ext": "pdf",
      },
      {
        "name": "feb-statement",
        "ext": "pdf",
      }
    ],
    "batch": "8012",
    "type": "ECS"
  }
]

I am looking for a N1QL query to select the dynamic key containing the arrays with name and extension. The result I am looking for should be:

[
      {
        "name": "license",
        "ext": "jpg",
      },
      {
        "name": "jan-statement",
        "ext": "pdf",
      },
      {
        "name": "feb-statement",
        "ext": "pdf",
      }
]

Answer 1

As you have dynamic field name in the batch field you can use dynamic reference (dictionary reference, no field name between dot and array bracket) using the following. d.[d.batch] ===> d.batch must evaluate to string ===> d.["7163"] ==> d.`7163` ==> returns value of the field `7163`

SELECT du.*
FROM default AS d
UNNEST d.[d.batch] AS du
WHERE d.type = "ECS";

You can also explore OBJECT functions (OBJECT_NAMES(), OBJECT_VALUES(), OBJECT_PAIRS()) convert dynamic object into ARRAY and iterate over it. https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html