JSON Module

This module contains functions for working with JSON data. You can use it to process JSON that is embedded in other file formats. For example, you can query JSON that is stored as lines in a large text file by using json:parse-as-xml with the text:collection function.

Processing large JSON files in parallel is not currently supported.

Built-in Functions for Reading JSON

To use the built-in functions in your query, you must import the JSON module as follows:

import module "oxh:json";

The JSON module contains the following functions:

json:parse-as-xml

Parses a JSON value as XML.

Signature

json:parse-as-xml($arg as xs:string?) as element(*)?

Parameters

$arg

Can be the empty sequence.

Returns

An XML element that models the JSON value. An empty sequence if $arg is an empty sequence.

Notes

About Converting JSON Objects to XML

JSON objects are similar to Avro maps and are converted to the same XML structure. See "Reading Maps."

For example, the following JSON object is converted to an XML element:

{ 
   "user" : "john", 
   "full_name" : "John Doe", 
   "age" : 45 
}

The object is modeled as the following element:

<oxh:item>
    <oxh:entry key="user">john</oxh:entry>
    <oxh:entry key="full_name">John Doe</oxh:entry>
    <oxh:entry key="age">45</oxh:entry>
</oxh:item>

About Converting JSON Arrays to XML

JSON arrays are similar to Avro arrays and are converted to the same XML structure. See "Reading Arrays."

For example, the following JSON array is converted to an XML element:

[ "red", "blue", "green" ]

The array is modeled as the following element:

<oxh:item>
   <oxh:item>red</oxh:item>
   <oxh:item>blue</oxh:item>
   <oxh:item>green</oxh:item>
</oxh:item>

About Converting Other JSON Types

The other JSON values are mapped as follows:

Table 6-4 JSON Type Conversions

JSON XML

null

An empty (nilled) element

true/false

xs:boolean

number

xs:decimal

string

xs:string

json:get

Retrieves an entry from a JSON object modeled as XML.

Signature

json:get($key as xs:string?, $obj as node()?) as element(oxh:entry)?

Or

json:get($key as xs:string?) as element(oxh:entry)?

Parameters

$key

The JSON data key.

$obj

The JSON object value.

Returns

The value of the following XPath expression:

$obj/oxh:entry[@key eq $key]

If $input not present, the behavior is identical to calling the two-argument function using the context item for $obj. See the Notes.

Notes

The following are equivalent:

$var/json:get("key")

json:get("key", $var)
  
$var/oxh:entry[@key eq "key"]

$var is a JSON object modeled as XML. See "Reading Maps."

Examples of JSON Functions

These examples query the following text file in HDFS.

 mydata/users-json.txt
 
{ "user" : "john", "full name" : "John Doe", "age" : 45 }
{ "user" : "kelly", "full name" : "Kelly Johnson", "age" : 32 }
{ "user" : "laura", "full name" : "Laura Smith", "age" : null }
{ "user" : "phil", "full name" : "Phil Johnson", "age" : 27 }
Example 1   

The following query selects the names of users that are older than 30:

import module "oxh:text";
import module "oxh:json";

for $line in text:collection("mydata/users-json.txt")
let $user := json:parse-as-xml($line)
where $user/json:get("age") gt 30
return 
   text:put-text($user/json:get("full name"))

This query generates text files that contain the following lines:

John Doe
Kelly Johnson
Example 2   

The next query selects the names of employees that have a null age value:

import module "oxh:text";
import module "oxh:json";

for $line in text:collection("mydata/users-json.txt")
let $user := json:parse-as-xml($line)
where $user/json:get("age")/nilled()
return 
   text:put-text($user/json:get("full name"))

This query generates a text file that contains the following line:

Laura Smith