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?

score 2 · Answer 1 · answered Mar 24 '21 at 08:25

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!

I noticed that the most nested array in the JSON document needed to be corrected to work. — Tim Meagher, Mar 24 '21 at 17:29
I see you edited the JSON example, but it was working for me like it was. With an items array inside an items array, you will need to adjust the context XPath accordingly.. — grtjn, Mar 24 '21 at 21:22

MarkLogic TDE XPath Values from nested JSON arrays

1 Answers1