MarkLogic TDE XPath Values from nested JSON arrays

Question

Although MarkLogic TDE Xpath values from JSON string array has helped, I'm still having difficulty accessing values from nested JSON arrays.

Here's a sample JSON document with nested arrays:

  { 
    "instance": {
      "ID": 7,
      "groups": [
        {
          "type": "parent",
          "items": [
            {
              "type": "child",
              "items": [
                {
                  "payload": {
                    "name": "Frank Lee",
                    "age": 22
                  }
                },
                {
                  "payload": {
                    "name": "Sal Lee",
                    "age": 21
                  }
                },
                {
                  "payload": {
                    "name": "Ro Mance",
                    "age": 27
                  }
                }
              ]          
            }
          ]
        }
      ]
    }
  }

With the following template, I can provide a SQL view consisting of the Instance ID and the group array type name:

xquery version "1.0-ml"; 

import module namespace tde = "http://marklogic.com/xdmp/tde" 
       at "/MarkLogic/tde.xqy";

let $nested-json :=
<template xmlns="http://marklogic.com/xdmp/tde">
  <context>/instance/array-node()/groups</context>
  <enabled>true</enabled>
  <rows>
    <row>
      <schema-name>JSON_Example</schema-name>
      <view-name>nested_json</view-name>
      <columns>
        <column>
          <name>instance_ID</name>
          <scalar-type>int</scalar-type>
          <val>../../ID</val>
        </column>
        <column>
          <name>group_type</name>
          <scalar-type>string</scalar-type>
          <val>type/data()</val>
          <nullable>true</nullable>
          <invalid-values>ignore</invalid-values>
        </column>
      </columns>
    </row>
  </rows>
</template>

(:return tde:validate($nested-json):)
return tde:template-insert("/nested_json.xml", $nested-json)

However, trying to create a view of values within the nested item array, say for the payload name is very challenging. This template does not work:

xquery version "1.0-ml"; 

import module namespace tde = "http://marklogic.com/xdmp/tde" 
       at "/MarkLogic/tde.xqy";

let $nested-json :=
<template xmlns="http://marklogic.com/xdmp/tde">
  <context>/instance/array-node()/groups/array-node()/items/array-node()/item/payload</context>
  <enabled>true</enabled>
  <rows>
    <row>
      <schema-name>JSON_Example</schema-name>
      <view-name>nested_json_array</view-name>
      <columns>
        <column>
          <name>instance_ID</name>
          <scalar-type>int</scalar-type>
          <val>../../../../ID</val>
        </column>
        <column>
          <name>group_type</name>
          <scalar-type>string</scalar-type>
          <val>name</val>
          <nullable>true</nullable>
          <invalid-values>ignore</invalid-values>
        </column>
      </columns>
    </row>
  </rows>
</template>

(:return tde:validate($nested-json):)
return tde:template-insert("/nested_json_array.xml", $nested-json)

Any ideas?

Answer 1

The issue is that the way XPath works on JSON in MarkLogic can cause confusion. Some levels are implicitly skipped, without you noticing it. It helps to understand how the full JSON tree looks like in XPath terms. The correct and most explicit context path is:

<context>/object-node()/object-node('instance')/array-node('groups')/object-node()/array-node('items')/object-node()/array-node('item')/object-node()/object-node('payload')</context>

which shows that you have to go 7 levels up to reach ancestor instance:

<val>../../../../../../../ID</val>

This doesn't add to readability, so as alternative, you could leverage ancestor axis, and simplify the XPath expressions. So use this as context:

<context>/instance/groups/items/item/payload</context>

and this to reach ID:

<val>ancestor::instance/ID</val>

HTH!