JSONiq for XQuery users

This tutorial introduces the JSONiq language, which declaratively manipulates JSON data.

Why don't you go ahead can try the queries of this document on our online demo interface?

http://www.zorba-xquery.com/html/jsoniq

# JSON

As explained on http://www.json.org/, JSON is a lightweight data-interchange format designed for humans as well as for computers. It supports as values:

1. objects (string-to-value map)
2. arrays (ordered sequence of values)
3. strings
4. numbers
5. booleans (true, false)
6. null

JSONiq extends XQuery to query and update JSON data, like XML data.

# Elevator Pitch

Here is an appetizer before we start the tutorial from scratch.

let $stores :=

[

{ "store number" : 1, "state" : "MA" },

{ "store number" : 2, "state" : "MA" },

{ "store number" : 3, "state" : "CA" },

{ "store number" : 4, "state" : "CA" }

]

let $sales := [

{ "product" : "broiler", "store number" : 1, "quantity" : 20 },

{ "product" : "toaster", "store number" : 2, "quantity" : 100 },

{ "product" : "toaster", "store number" : 2, "quantity" : 50 },

{ "product" : "toaster", "store number" : 3, "quantity" : 50 },

{ "product" : "blender", "store number" : 3, "quantity" : 100 },

{ "product" : "blender", "store number" : 3, "quantity" : 150 },

{ "product" : "socks", "store number" : 1, "quantity" : 500 },

{ "product" : "socks", "store number" : 2, "quantity" : 10 },

{ "product" : "shirt", "store number" : 3, "quantity" : 10 }

]

let $join :=

for $store in jn:members($stores), $sale in jn:members($sales)

where $store("store number") = $sale("store number")

return {

"nb" : $store("store number"),

"state" : $store("state"),

"sold" : $sale("product")

}

return [$join]

[

{ "nb" : 1, "state" : "MA", "sold" : "broiler" },

{ "nb" : 1, "state" : "MA", "sold" : "socks" },

{ "nb" : 2, "state" : "MA", "sold" : "toaster" },

{ "nb" : 2, "state" : "MA", "sold" : "toaster" },

{ "nb" : 2, "state" : "MA", "sold" : "socks" },

{ "nb" : 3, "state" : "CA", "sold" : "toaster" },

{ "nb" : 3, "state" : "CA", "sold" : "blender" },

{ "nb" : 3, "state" : "CA", "sold" : "blender" },

{ "nb" : 3, "state" : "CA", "sold" : "shirt" }

]

# And here you go

## JSONiq types

JSONiq maps JSON types to the XQuery data model. Numbers are xs:integer, xs:decimal or xs:double, strings are xs:string, true and false are xs:boolean and null is the unique value of a new atomic type jn:null.

By default, in JSONiq, for convenience, true, false and null are recognized as literals instead of the classical XQuery path expression semantics (i.e., they would otherwise navigate to XML elements named "true", "false" or "null"). However, this can be deactivated to use the path expression semantics.

JSONiq also introduces new items: objects and arrays. Objects are sets of key/value pairs. Arrays have members which are values. Values are objects, arrays, XML nodes, functions or atomic items. Note that, in particular, Arrays can nest, unlike sequences.

The new item types for objects and arrays are object() and array(). json-item() is a supertype of both. structured-item() is a supertype for json-item() and node().

## JSON Constructors

JSONiq introduces JSON constructors, in a similar way to XML constructors.

You can put any expression in a array. The items in the sequence produced by the expression will become members of the array:

[ 1 to 10 ]

[ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ]

Or you can dynamically compute an object:

{

"Greeting" : let $d := "Mister Spock"

return concat("Hello, ", $d),

"Farewell" : string-join(("Live", "long", "and", "prosper"),

" ")

}

{ "Greeting" : "Hello, Mister Spock", "Farewell" : "Live long and prosper" }

You can also dynamically generate singleton objects:

{ concat("Square of ", 2) : 2 * 2 }

{ "Square of 2" : 4 }

and then use the {| |} construct to wrap several of them in a bigger object:

{|

for $i in 1 to 10

return { concat("Square of ", $i) : $i * $i }

|}

{

"Square of 1" : 1,

"Square of 2" : 4,

"Square of 3" : 9,

"Square of 4" : 16,

"Square of 5" : 25,

"Square of 6" : 36,

"Square of 7" : 49,

"Square of 8" : 64,

"Square of 9" : 81,

"Square of 10" : 100

}

## JSON as a subset of JSONiq

As a rule of thumb, a well-formed JSON document is a JSONiq expression as well. This means that you can copy-and-paste a JSON document into a query. The following are JSONiq queries that are "idempotent" (they just output themselves):

{ "pi" : 3.14, "sq2" : 1.4 }

{ "pi" : 3.14, "sq2" : 1.4 }

[ 2, 3, 5, 7, 11, 13 ]

[ 2, 3, 5, 7, 11, 13 ]

{

"operations" : [

{ "binary" : [ "and", "or"] },

{ "unary" : ["not"] }

],

"bits" : [

0, 1

]

}

{

"operations" : [

{ "binary" : [ "and", "or" ] },

{ "unary" : [ "not" ] }

],

"bits" : [

0, 1

]

}

This works with objects, arrays (even nested), strings, numbers, booleans, null. The exceptions to this rule (but we are working on it!) are that:

(i) empty objects are not recognized

(ii) characters escaped with the \ in JSON strings are not recognized, XML character references are recognized instead.

It also works the other way round: if your query outputs an object or an array, you can directly use it as a JSON document.

# JSON Navigation

Up to now, you learnt how to compose expressions so as to do some computations and to build objects and arrays. It also works the other way round: if you have some JSON data, you can access it and navigate.

All you need to know is: JSONiq views

1. an array as an ordered list of values,
2. an object as a set of name/value pairs

## Objects

You can use an object as if it were a function and pass the call an argument of type xs:string. It will return the value associated thereto:

let $person := {

"first name" : "Sarah",

"age" : 13,

"gender" : "female",

"friends" : [ "Jim", "Mary", "Jennifer"]

}

return $person("first name")

"Sarah"

You can also ask for all keys in an object:

let $person := {

"name" : "Sarah",

"age" : 13,

"gender" : "female",

"friends" : [ "Jim", "Mary", "Jennifer"]

}

return { "keys" : [ jn:keys($person)] }

{ "keys" : [ "name", "age", "gender", "friends" ] }

## Arrays

You can use an array as if it were a function and pass the call an argument of type xs:integer. It will return the entry at that position:

let $friends := [ "Jim", "Mary", "Jennifer"]

return $friends(2)

Mary

It is also possible to get the size of an array:

let $person := {

"name" : "Sarah",

"age" : 13,

"gender" : "female",

"friends" : [ "Jim", "Mary", "Jennifer"]

}

return { "how many friends" : jn:size($person("friends")) }

{ "how many friends" : 3 }

For convenience, there is a function that returns all elements in an array, as a sequence:

let $person := {

"name" : "Sarah",

"age" : 13,

"gender" : "female",

"friends" : [ "Jim", "Mary", "Jennifer"]

}

return jn:members($person("friends"))

Jim Mary Jennifer

# Relational Algebra

Remember SQL's SELECT FROM WHERE statements? JSONiq inherits selection, projection and join capability from XQuery FLWOR expressions. In order to traverse an array, jn:members() converts it into a sequence which can then be iterated over by a FLWOR expression.

let $stores :=

[

{ "store number" : 1, "state" : "MA" },

{ "store number" : 2, "state" : "MA" },

{ "store number" : 3, "state" : "CA" },

{ "store number" : 4, "state" : "CA" }

]

let $sales := [

{ "product" : "broiler", "store number" : 1, "quantity" : 20 },

{ "product" : "toaster", "store number" : 2, "quantity" : 100 },

{ "product" : "toaster", "store number" : 2, "quantity" : 50 },

{ "product" : "toaster", "store number" : 3, "quantity" : 50 },

{ "product" : "blender", "store number" : 3, "quantity" : 100 },

{ "product" : "blender", "store number" : 3, "quantity" : 150 },

{ "product" : "socks", "store number" : 1, "quantity" : 500 },

{ "product" : "socks", "store number" : 2, "quantity" : 10 },

{ "product" : "shirt", "store number" : 3, "quantity" : 10 }

]

let $join :=

for $store in jn:members($stores), $sale in jn:members($sales)

where $store("store number") = $sale("store number")

return {

"nb" : $store("store number"),

"state" : $store("state"),

"sold" : $sale("product")

}

return [$join]

[

{ "nb" : 1, "state" : "MA", "sold" : "broiler" },

{ "nb" : 1, "state" : "MA", "sold" : "socks" },

{ "nb" : 2, "state" : "MA", "sold" : "toaster" },

{ "nb" : 2, "state" : "MA", "sold" : "toaster" },

{ "nb" : 2, "state" : "MA", "sold" : "socks" },

{ "nb" : 3, "state" : "CA", "sold" : "toaster" },

{ "nb" : 3, "state" : "CA", "sold" : "blender" },

{ "nb" : 3, "state" : "CA", "sold" : "blender" },

{ "nb" : 3, "state" : "CA", "sold" : "shirt" }

]

# Access external data

Our implementation supports collections of (and indices on) JSON objects or arrays:

dml:collection("my:data")

{ "foo" : "Your" }

{ "foo" : "Collection" }

{ "foo" : "of" }

{ "foo" : "JSON" }

{ "foo" : "objects" }

It is also possible to get JSON content with an HTTP request, or by parsing it from a string. The EXPath http-client module (described in the Zorba documentation) allows you to make HTTP requests, and the jn:parse-json() function allows you to use the body as an object or an array.

# JSON and XML

You can use XML and JSON in the same program. An XML node can be a value in an object or array, however, arrays and object may not be children of an XML node - but you can extract atomic values or XML nodes inside objects and arrays to insert them in a new XML node.

let $data := {

"color" : "blue",

"closed" : true,

"points" : [[10,10], [20,10], [20,20], [10,20]]

}

let $stroke := attribute stroke { $data("color") }

let $points := attribute points { jn:flatten($data("points")) }

return

if ($data("closed")) then

<svg><polygon>{ $stroke, $points }</polygon></svg>

else

<svg><polyline>{ $stroke, $points }</polyline></svg>

<?xml version="1.0" encoding="UTF-8"?>

<svg><polygon stroke="blue" points="10 10 20 10 20 20 10 20"/></svg>

# I want more

JSONiq supports JSON updates by extending the XQuery Update Facility specification, so you can declaratively update your JSON data. JSONiq provides new expressions that produce update primitives on JSON items. The list of updates that is eventually output by your program is then applied to your JSON data.

copy $people := {

"John" : { "status" : "single" },

"Mary" : { "status" : "single" } }

modify (replace json value of $people("John")("status") with "married",

replace json value of $people("Mary")("status") with "married")

return $people

{ "John" : { "status" : "married" }, "Mary" : { "status" : "married" } }

JSONiq works with the XQuery 3.0 standard (switch, typeswitch and try-catch expressions, universal/existential quantifiers, path expressions, filtering expressions, functors, mappings, grouping, windowing will work). The Zorba implementation is also compatible with the proprietary Zorba scripting.

The complete JSONiq specification is available on http://jsoniq.org/