Working with JSON in XQuery

Blog

Working with JSON in XQuery

6 October, 2020
By Dave Cassel
No Comments

MarkLogic supports XQuery and JavaScript as native languages. 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.

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 objects. I’ll give recursive descent its own post later; for now, I’ll talk about objects.

A json:object is a mutable key-value structure (this is the same as a map:map, except that the ordering of keys in a map:map is undefined and its default serialization is JSON rather than XML). It can be hierarchical, because the values can themselves be objects. 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 json:object structure. Then we can use map:put() or map:with 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)

Note that while objects 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() => map:with("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 Nodes. 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 objects, 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 objects; you’ll find it runs faster.

Wrap Up

While XQuery/XML and JavaScript/JSON are good pairings, you can easily work with either structure from either language. Your can figure out from your data which structure is a better fit for your data. Your language choice can be driven by the needs and knowledge of the development team.

Share this post: