JSONQuery: Data Querying Beyond JSONPath

By on July 16, 2008 12:04 am
Notice: We recommend reading our newer post on RQL.

A new data querying tool for has been added to Dojo 1.2. JSONQuery is a new module intended to succeed and improve upon the JSONPath module introduced in Dojo 1.1. JSONQuery provides a comprehensive set of data querying tools including filtering, recursive search, sorting, mapping, range selection, and flexible expressions with wildcard string comparisons and various operators.

JSONQuery provides safe evaluation with language agnostic expressions that prevents arbitrary code execution. It also uses intuitive result-based evaluation that allows successive query operations. Furthermore, the new JSONQuery module provides significant performance improvements, with 20-100x faster execution with the common filter operation on large arrays than the JSONPath module. JSONQuery generally supersets the functionality of JSONPath and provides syntax that matches and behaves like JavaScript where the syntax intersects for maximum ease of use.

Usage API

A JSONQuery can be executed with the following call:

results = dojox.json.query(query,object);

Where query is the JSONPath query to execute and object is the root object or array to query. You can also create a “compiled” evaluation function that can be reused for multiple evaluations by only passing a query string:

evaluator = dojox.json.query(query);

The evaluator function can be then be performed on a query on data by calling it with the data as a parameter:

results = evaluator(object);

In situations where a single query may be executed multiple times, doing a single parse/compilation with dojox.json.query(query) and reusing the returned evaluation function will provide better performance. It is worth noting that dojox.json.query(query,object) is functionally equivalent to dojox.json.query(query)(object).

Query Syntax

JSONQuery evaluations begin with the provided object, which can referenced within queries with $. From the starting object, various operators can be successively applied, each operating on the result of the last operation. You may explicitly begin your query with $, but this is implicitly auto-inserted, so it is not necessary. This allows you to start queries with operators. JSONQuery uses syntax that is similar to JavaScript (with a number of extra operators), so a simple query could be performed like:

data = {foo:"bar"};
results = dojox.json.query("$.foo",data);
results -> "bar"

Query Capabilities

JSONQuery provides all the functionality of JSONPath, and additional query capabilities. By analogy, JSONQuery is to JSONPath, as XQuery is to XPath. In particular, JSONQuery provides the expressive equivalence of XQuery FLOWR expressions for mapping, sorting, and filtering object data. Several of the fundamental operators of JSONQuery follow the same syntax as JavaScript, as well behaving exactly as with JavaScript evaluation:

  • .property – This will return the provided property of the object
  • [expression] – This returns the property name/index defined by the evaluation of the provided expression

For example, the following JSONQuery will find the foo property of the 2nd item in the provided array (the $ is omitted):

[1].foo

The following operators are new in JSONQuery:

  • [?expression] – This will perform a filter operation on an array, returning all the items in an array that match the provided expression. This operator does not need to be in brackets, you can simply use ?expression, but since it does not have any containment, no operators can be used afterward when used without brackets. The following JSONQuery will find all the array items that have a price less than 15:

    [?price < 15]

    And to add a condition for the rating property to be greater than 3 (and omit the brackets):

    ?price < 15 & rating > 3
  • [/expression], [\expression], [/expression, /expression] - This performs a sort operation on an array, with sort based on the provide expression. Multiple comma delimited sort expressions can be provided for multiple sort orders (first being highest priority). / indicates ascending order and \ indicates descending order. For example to sort an array by lastName first and then firstName as the second priority:
    [/lastName,/firstName]
  • [=expression] - This performs a map operation on an array, creating a new array with each item being the evaluation of the expression for each item in the source array. For example, to create a list of the price value from an array of objects, we could use the query:
    [=price]

    You can also use object literals and and create a new array of objects that with a name and price properties generated from the source array:

    [={price:price,name:firstName + " " + lastName}]
  • expr = expr - Performs a comparison (like JavaScript's ==). When comparing to a string, the comparison string may contain wildcards * (matches any number of characters) and ? (matches any single character). For example to find all objects in an array where the name starts with "Mr", one could use the query:
    [?name='Mr*']
  • expr ~ expr - Performs a string comparison with case insensitivity. For example to find all objects in an array with the word "the" in the description regardless of case:
    [?description~'*the*']
  • ..[?expression] - This will perform a deep search filter operation on all the objects and subobjects of the current data. Rather than only searching an array, this will search property values, arrays, and their children.
  • $1, $2, $3... - This can be used to reference additional parameters passed to the query call. For example:
    results = dojox.json.query("[?firstName=$1&lastName=$2]",
    					myData,"John","Doe");

    or it can be applied to the evaluator function:

    evaluator = dojox.json.query("[?firstName=$1&lastName=$2]");
    results = evaluator(myData,"John","Doe");

The following operators from JSONPath are also supported:

  • [start:end:step] - This performs an array slice/range operation, returning the elements from the optional start index to the optional end index, stepping by the optional step parameters. For example to get the first ten items in an array:
    [0:10]
  • [expr,expr] - The union operator returns an array of all the property/index values from the evaluation of the comma delimited expressions.
  • .* or [*] - Returns the values of all the properties of the current object.
  • $ - This is the root object.
  • @ - This is the current object in filter, sort, and map expressions. Note that names are auto-converted to property references of the current object in expressions, but @ can be used for index access on the current object. The following queries are identical:
    [?name='Fred']
    [?@.name='Fred']
    [?@['name']='Fred']
  • ..property - Performs a recursive search for the given property name, returning an array of all values with such a property name in the current object and any subobjects.
  • +, -, /, *, &, |, %, (, ), <, >, <=, >=, != - These operators behave just as they do in JavaScript.

Multiple operators can be used successively to create complex queries. For example, to find all the objects from the array in the products property that have a price under 15 and then sort them by descending order of rating and show the first twenty items from the resultant list, we could query:

$.products[?price < 15][\rating][0:20]

Queries can use the regular operators to form general expressions based on more complex query operations. For example, to find the difference between the lowest priced item and the highest priced item in an array:

$.store.book[\price][0].price - $.store.book[/price][0].price

Summary

The new Dojo JSONQuery module provides a powerful tool for general purpose data querying, and can be used in variety of situations. The JSONQuery module is already used by Persevere to parse and execute queries in it's server side JavaScript object storage environment. JSONQuery is a flexible and complete query format for handling large JSON/object data structures with an intuitive JavaScript-like syntax.

Comments

  • This looks awesome! Is it efficient? If so, should it be a supported method for JsonRestStore’s getValue method? And/Or perhaps be a supported syntax for the upcoming client side querying of store data?

  • JSONQueries are “compiled” to JavaScript, so it is generally close to the same efficiency as writing the full JavaScript to do the operation. This won’t be included in the generic data stores, to keep them light, but a data store can certainly use it if desired. The Persevere data store will use JSONQuery on the client side, since Persevere will also now support JSONQuery on the server.

  • steven

    I had a problem using [=expression] syntax. Please refer to the code:

    var g = { “name”: “Asia”, “type”: “continent”, “children”: [ { “name”: “China”, “type”: “country” }, { “name”: “India”, “type”: “country” }, { “name”: “Russia”, “type”: “country” }, { “name”: “Mongolia”, “type”: “country” } ] };
    dojox.json.query(“..[= name]”, [g])

    // output is
    ,Asia,,China,India,Russia,Mongolia

    Please note the comma before Asia and China, which are not supposed to be there. Is this a bug?

  • @steven: Good catch. The current version treats all children arrays as objects as well, and the arrays don’t have a “name” property, so their name is undefined and entered into the resultant array as such (which is empty when serialized with some JSON serializers). I will change the logic so it only finds arrays on recursive searches. BTW, you should be able to get the same results with:
    dojox.json.query(“..[= name]”, g) // no array around g

  • Pingback: SitePen Blog » Getting Started with Persevere Using Dojo()

  • Pingback: Release Notes zum Dojo Toolkit V1.2 - dojotoolkit-forum.de()

  • Pingback: SitePen Blog » JSON Schema in Dojo()

  • Pingback: SitePen Blog » More Query and Caching Power for Data Stores: ClientFilter, JsonQuery, and JsonQueryRestStore()

  • Vili

    Hi,

    JsonQuery is great! I’ve implemented in several scenarios, even extended sorting with “dotted” attribute support for a more generic usage. One thing I came across though: when a store attribute value contains new lines pattern matching against this attribute will not result in success. Any suggestion how to get around this?

  • @Vili:
    Good catch, I just checked in a fix for this for 1.3.

  • Pingback: Browser storage: Do we need SQL? Or would a JSON approach be better? on Dion Almaer's Blog()

  • Pingback: Ajaxian » Browser storage: What is the correct API? SQL? JSON?()

  • lightflowmark

    Hi,
    I’m looking around for a more in-depth guide to JSONQuery syntax and haven’t been able to find one – any pointers?

    Specifically, I’m looking for an analogy to SQLs someColumn IN (value,value2,value3) syntax. I can use a whole enormous string of ORs, but it doesn’t seem great.

    Is this part of the JSONQuery spec? I would think that something like someProperty.In({1,8,2,80}) would be useful.

    Yours,
    Mark

  • Pingback: trickyco.de » Blog Archive » The “Open Web”, my spin.()

  • Piyush D

    When we query the JSON object and the query returns no result, the script stops behaving properly. What is the work around of this scenario?

  • @Piyush: Do you have any specifics on how to reproduce this? I have not seen this issue in any of our tests.

  • Andreas Hartmann

    Hi everyone,

    does someone know if a Java-based JSONQuery parser exists? I’d like to analyze the queries on the server side and build DB queries accordingly.

    Thanks!
    — Andreas

  • Pingback: SitePen()

  • Zhaidarbek

    @Andreas Hartmann: I guess it can be found in Persevere java sources, though i don’t know which class is responsible for parsing incoming JSONQuery string.

  • Zhaidarbek

    Probably this url can give you a starting point http://sites.google.com/site/persvr/documentation/using-persevere-from-java
    You need to check out sources first.

  • Pingback: Persevere, Node.JS y HelmaNG | HyperCODE()

  • Paresh

    Hellow,
    I am using jQuery and for querying JSON I need to use the JSONQuery.
    I tried the dojo implementation of the JSONQuery and it works fine, but when I include dojo it includes many other javascripts which I do not need, but I dint found any such suggestion for how to exclude other javascripts that comes when u use the dojo.js.
    I tried the standalone version of the JSONQuery from http://github.com/JasonSmith/jsonquery but that does not work proper when I try to give sort expression on number.
    can anyone suggest good work around?

  • nukeUser

    How would one tackle data conversions? For example, if a long date is part of the data set, how does one evaluate a date/format or other data manipulation?

    Could something like this work?
    dojox.jsonPath.query(data[={apptDt:new Date(date_node)}]

  • Pingback: Size/speed of various MARC serializations using ruby-marc » Robot Librarian()

  • Mark

    I am trying to use a wild card and it is not working. Here is my code:

    var aryBoats = dojox.jsonPath.query(jsnPeoples, “$.peoples..boats[?name=’wav*’]”)
    console.log(aryBoats)

    It is returning an empty array. Can you tell me why it would not find any boats with wav in the name?

    I know for a fact that I have boats with wav in them because when I do the following: var aryBoats = dojox.jsonPath.query(jsnPeoples, “$.peoples..boats”) it returns the following:

    [[Object { num=0, name=”Take Me Home”, more…}, Object { num=1, name=”Creatin waves”, more…}], [Object { num=2, name=”Makin waves”, more…}, Object { num=3, name=”wavin”, more…}], [Object { num=0, name=”Take Me Home”, more…}, Object { num=1, name=”Creatin waves”, more…}], [Object { num=0, name=”Take Me Home”, more…}, Object { num=1, name=”Creatin waves”, more…}], [Object { num=0, name=”Take Me Home”, more…}, Object { num=1, name=”Creatin waves”, more…}], [Object { num=0, name=”Take Me Home”, more…}, Object { num=1, name=”Creatin waves”, more…}]]

  • Pingback: What tools for selecting JSON elements do you find useful? - Quora()

  • Tejasvi Singh (Leo)