Working with JSON in XQuery

Author: Dave Cassel  |  Category: Software Development

MarkLogic supports two native languages, XQuery and JavaScript. XQuery is a very natural way to work with XML, just as JavaScript is a very natural way to work with JSON. However, sometimes it’s useful to cross over and work with JSON using XQuery or vice versa. This post has some tips on using XQuery to work with JSON in memory (to update JSON in the database, use the xdmp:node* functions).

JSON Nodes

The first thing to know is that JSON that we get from the database comes in an immutable form. Specifically, that’s a JSON Node. We can directly construct JSON Nodes using constructors, like this:

object-node {
  "a": 1,
  "b": 2
}

If all you want to do is retrieve a node from the database, or a part of one, working with nodes is great. One of the cool things is that we can apply XPath to nodes:

let $obj := object-node {
  "a": 1,
  "b": 2
}
return $obj/a

This makes it easy to select a portion of a JSON document; we might do that when we pull original JSON content out of an envelope document, for instance. We can also use this to pull data that is deep in a node structure, using a path like “/envelope/instance/TopProperty/lowerProperty”.

Changing

If we want to edit a JSON structure, we need a different approach. There are two ways we can go about it: recursive descent over JSON nodes, or by converting to maps. I’ll give recursive descent its own post later; for now, I’ll talk about maps.

A map:map is a mutable key-value structure. It can be hierarchical, because the values can themselves be maps. This should sound familiar: I could give that same description for a JavaScript object. If fact, when we want to represent JSON nodes in a mutable way, the way to do it is to convert them to the map:map structure. Then we can use map:put() to change an existing value or add a new one, and map:delete() to delete an existing value. When we’re done, we can convert back to JSON to update the database or send on to a client.

let $obj := object-node {
  "a": 1,
  "b": 2
}
let $map := xdmp:from-json($obj)
let $_ := map:put($map, "c", 3)
return xdmp:to-json($map)

It’s worth noting here that what we get back from xdmp:from-json() isn’t just a standard map:map, but a json:object. These behave very much the same, except that 1) its default serialization is as JSON, rather than the XML that map:map uses, and 2) it maintains the key order.

Note that while maps are great for manipulating data, we can’t apply an XPath like we can with a JSON node.

What Am I Looking At?

Query Console cheerfully presents JSON data such that it looks like JSON, regardless of whether you’re looking at JSON nodes or json:objects. This can make it hard to know what exactly you’re looking at. Knowing the format of your JSON data tells you how to interact with it. You can identify an item of each representation using instance of tests.

declare function local:report($item)
{
  "object node: " || $item instance of object-node() ||
  "; json:object: " || $item instance of element(json:object) ||
  "; map:entry: " || $item instance of map:map
};

let $node := object-node { "foo": "bar" }
let $obj := json:object() 
let $_ := map:put($obj, "foo", "bar")
let $json-obj := <r>{$obj}</r>/node()
let $map := map:new(map:entry("foo", "bar"))
let $to-json := xdmp:to-json($obj)/node() (: xdmp:to-json returns a document node :)
let $to-json-map := xdmp:to-json($map)/node()
let $from-json := xdmp:from-json($node)
return (
  "node:        " || local:report($node),
  "json-obj:    " || local:report($json-obj),
  "map:         " || local:report($map),
  "obj:         " || local:report($obj),
  "to-json:     " || local:report($to-json),
  "to-json-map: " || local:report($to-json-map),
  "from-json:   " || local:report($from-json),
  "fn:data:     " || local:report(fn:data($node))
)

Results:

node:        object node: true; json:object: false; map:entry: false
json-obj:    object node: false; json:object: true; map:entry: false
map:         object node: false; json:object: false; map:entry: true
obj:         object node: false; json:object: false; map:entry: true
to-json:     object node: true; json:object: false; map:entry: false
to-json-map: object node: true; json:object: false; map:entry: false
from-json:   object node: false; json:object: false; map:entry: true
fn:data:     object node: false; json:object: false; map:entry: true

Constructing

There are two ways of building JSON data. We can use the JSON node constructors directly, or we can build up maps and pass the result to xdmp:to-json(). Here’s the constructor version:

object-node {
  "a": 1,
  "b": 2,
  "c": array-node {
    object-node { "fname": "Harrison", "lname": "Ford" },
    object-node { "fname": "Mark", "lname": "Hamill" },
    object-node { "fname": "Carrie", "lname": "Fisher" },
    object-node { "fname": "Natalie", "lname": "Portman" }
  }
}

And here’s the method using maps:

xdmp:to-json(
  json:object() =>
    map:with("a", 1) => 
    map:with("b", 2) =>
    map:with("c", 
      json:array() =>
        json:array-with((
          json:object() => map:with("fname", "Harrison") => map:with("lname", "Ford"),
          json:object() => map:with("fname", "Mark") => map:with("lname", "Hamill"),
          json:object() => map:with("fname", "Carrie") => map:with("lname", "Fisher"),
          json:object() => map:with("fname", "Natalie") => map:with("lname", "Portman")
        ))
      )
)

Personally, I find the direct constructor approach more natural. An important consideration is what you’re going to do with the structures once you have them. If you plan to modify them, go with the maps, do whatever modification you need, and then pass the finished product to xdmp:to-json — don’t convert back and forth. Likewise, if you’re constructing JSON to return to a client and won’t be persisting it in the database, stick with maps; you’ll find it runs faster.

Wrap Up

A little understanding goes a long way. I’ve been working with JSON data using XQuery as part of the Smart Mastering project; having gotten a better understanding of the data structures involved will let me write better code.

Tags: , ,

Leave a Reply