JSONiq for XQuery users tutorial

JSON for XQuery

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?

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:

objects (string-to-value map)
arrays (ordered sequence of values)
strings
numbers
booleans (true, false)
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" : "socks" },

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

{ "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 }

[ 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

an array as an ordered list of values,
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.