JSONiq

The XQuery Vision in 2000

The World Wide Web promises to transform human society by making virtually all types of information instantly available everywhere. Two prerequisites for this promise to be realized are a universal markup language and a universal query language. The power and flexibility of XML make it the leading candidate for a universal markup language.

Don Chamberlin, Jonathan Robie, Daniela Florescu, 2000

www.almaden.ibm.com/cs/people/chamberlin/quilt_lncs.pdf

The XQuery Vision in 2000

XML provides a way to label information from diverse data sources including structured and semi-structured documents, relational databases, and object repositories.

The Extensible Markup Language, XML, is having a profoundly unifying effect on diverse forms of information. For the first time, XML provides an information interchange format that is editable, easily parsed, and capable of representing nearly any kind of structured or semi-structured information.

Don Chamberlin, Jonathan Robie, Daniela Florescu, 2000

www.almaden.ibm.com/cs/people/chamberlin/quilt_lncs.pdf

The JSON Vision

Unfortunately, XML is not well suited to data-interchange, much as a wrench is not well-suited to driving nails. It carries a lot of baggage, and it doesn't match the data model of most programming languages. When most programmers saw XML for the first time, they were shocked at how ugly and inefficient it was. It turns out that that first reaction was the correct one. There is another text notation that has all of the advantages of XML, but is much better suited to data-interchange. That notation is JavaScript Object Notation (JSON).

Douglas Crockford

http://www.json.org/xml.html

The JSON Vision

JSON is a better data exchange format. XML is a better document exchange format. Use the right tool for the right job.

Douglas Crockford

http://www.json.org/xml.html

JSON, XML, and Semi-Structured Data

JSONiq: XQuery for JSON

JSONiq: JSON for XQuery

JSON is often much simpler for intermediate results in a query, passing parameters to functions, returning results from functions..

JSONiq in a nutshell

{
  "name" : "Sarah",
  "age" : 13,
  "gender" : "female",
  "friends" : [ "Jim", "Mary", "Jennifer"]
}
      

$sarah("friends")(1)
      

JSONiq in a nutshell (2)

let $sarah := collection("users")[.("name") = "Sarah"]
return {
  "name" : "Amanda",
  "age" : $sarah("age") + 1,
  "gender" : "female",
  "friends" : $sarah("friends")
}
	

Adding JSON to XDM

A Grouping Query in JSONiq (Data)

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

Group sales by product, across stores.

A Grouping Query in JSONiq

{
  for $sales in collection("sales")
  let $pname := $sales("product")
  group by $pname
  return $pname : sum(for $s in $sales return $s("quantity"))
}   

{
  "blender" : 250,
  "broiler" : 20,
  "shirt" : 10,
  "socks" : 510,
  "toaster" : 200
}  	  

More complex grouping (Data)

{ "name" : "broiler", "category" : "kitchen", "price" : 100, "cost" : 70 },
{ "name" : "toaster", "category" : "kitchen", "price" : 30, "cost" : 10 },
{ "name" : "blender", "category" : "kitchen", "price" : 50, "cost" : 25 },
{ "name" : "socks", "category" : "clothes", "price" : 5, "cost" : 2 },
{ "name" : "shirt", "category" : "clothes", "price" : 10, "cost" : 3 }

{ "store number" : 1, "state" : CA },
{ "store number" : 2, "state" : CA },
{ "store number" : 3, "state" : MA },
{ "store number" : 4, "state" : MA }
        

More complex grouping

{
  for $store in collection("stores")
  let $state := $store("state")
  group by $state
  return
     $state : {
       for $product in collection("products")
       let $category := $product("category")
       group by $category
       return
         $category : {
            for $sales in collection("sales")
            where $sales("store number") = $store("store number")
              and $sales("product") = $product("name")
            let $pname := $sales("product")
            group by $pname
            return $pname : sum( for $s in $sales return $s("quantity") )
         }
      }
}	

More complex grouping

{
  $state : {
     $category : {
         $pname : sum( for $s in $sales return $s("quantity") )
     }
  }
}	

{
  "CA" : {
      "clothes" : {
         "socks" :  510
      },
      "kitchen" : {
         "broiler" : 20,
         "toaster" : 150
      }
  },
  "MA" : {
      "clothes" : {
         "shirt" : 10
       },
      "kitchen" : {
         "blender" : 250,
         "toaster" : 50
      }
  }
}

Views of Relational Data

[
    { "userid" : "W0342", "ticker" : "DIS", "shares" : 153212312 },
    { "userid" : "M0535", "ticker" : "DIS", "shares" : 10 },
    { "userid" : "M0535", "ticker" : "AIG", "shares" : 23412 }
]    

JSON is better than XML for this - XML is more complex for humans and for query optimizers.

Converting JSON <=> XML <=> HTML

JSON:

{
  "col labels" : ["singular", "plural"],
  "row labels" : ["1p", "2p", "3p"],
  "data" :
     [
        ["spinne", "spinnen"],
        ["spinnst", "spinnt"],
        ["spinnt", "spinnen"]
     ]
}

Query:

   (: Column headings :)
  {
      ,
     for $th in values(json("table.json")("col labels"))
     return { $th }
  }
  
  {  (: Data for each row :)
     for $r at $i in values(json("table.json")("data"))
     return
        
         {
           { values(json("table.json")("row labels")[$i]) },
           for $c in $r
           return { $c }
         }
        
  }
]]>
    

Sample Use Cases

Some of the use cases at http://jsoniq.org:

Python API

from jsoniq import Connection
connection = Connection("probono", "94306")
connection.set(sasl_mechs="GSSAPI", ssl="true", ssl_cert_alias="cert1")

try:
  connection.open()
  dbase = connection.risotto
  collection = dbase.developers

  a = {"name": {"last": "brantner", "first": "matthias" }, "role": "wizard" }
  b = {"name": {"last": "westmann", "first": "till" }, "role": "demigod" }

  collection.insert(a)
  collection.insert(b)

  q = """
     for $d in collection("developers")
     where $d("role") = "wizard"
     return $d
  """

# A simple query.
#
# query() prepares a query and executes it

  cursor = dbase.query(q)
  for c in cursor:
      json.dumps(c)

# A prepared query that binds variables

  q = """
     for $d in collection("developers")
     where $d("role") = $role
     return $d
  """

  query = dbase.prepare(q)
  query.bind(role="wizard")
  cursor = query.execute()
  for c in cursor:
    json.dumps(c)

# Inspecting free variables and bindings

  q = """
     for $d in collection("developers")
     where $d("role") = $role
     return $d
  """
  query = dbase.prepare(q)
  for f in query.free(): # returns all free variables
      if f:isbound:
        print f.name(), f.value()
      else
        print f.name(), " is not bound!"
        query.bind(f.name(), "comedian")

# The API expects an implementation to be smart enough to keep track
# of all open databases for a connection, all open queries for a
# database, etc., and to close them appropriately.

except ConnectionError,c:
  print c
finally:
  connection.close()

JSONiq and XQuery: quo vadis?

jsoniq.org

Available at http://jsoniq.org: