Is there a query language for JSON?

Question

Is there a (roughly) SQL or XQuery-like language for querying JSON?

I'm thinking of very small datasets that map nicely to JSON where it would be nice to easily answer queries such as "what are all the values of X where Y > 3" or to do the usual SUM / COUNT type operations.

As completely made-up example, something like this:

[{"x": 2, "y": 0}}, {"x": 3, "y": 1}, {"x": 4, "y": 1}]

SUM(X) WHERE Y > 0     (would equate to 7)
LIST(X) WHERE Y > 0    (would equate to [3,4])

I'm thinking this would work both client-side and server-side with results being converted to the appropriate language-specific data structure (or perhaps kept as JSON)

A quick Googling suggests that people have thought about it and implemented a few things (JAQL), but it doesn't seem like a standard usage or set of libraries has emerged yet. While each function is fairly trivial to implement on its own, if someone has already done it right I don't want to re-invent the wheel.

Any suggestions?

Edit: This may indeed be a bad idea or JSON may be too generic a format for what I'm thinking.. The reason for wanting a query language instead of just doing the summing/etc functions directly as needed is that I hope to build the queries dynamically based on user-input. Kinda like the argument that "we don't need SQL, we can just write the functions we need". Eventually that either gets out of hand or you end up writing your own version of SQL as you push it further and further. (Okay, I know that is a bit of a silly argument, but you get the idea..)

Answer 1

OK, this post is a little old, but... if you want to do SQL-like query in native JSON (or JS objects) on JS objects, take a look at https://github.com/deitch/searchjs

It is both a jsql language written entirely in JSON, and a reference implementation. You can say, "I want to find all object in an array that have name==="John" && age===25 as:

{name:"John",age:25,_join:"AND"}

The reference implementation searchjs works in the browser as well as as a node npm package

npm install searchjs

It can also do things like complex joins and negation (NOT). It natively ignores case.

It doesn't yet do summation or count, but it is probably easier to do those outside.

Answer 2

1. Google has a project called lovefield; just found out about it, and it looks interesting, though it is more involved than just dropping in underscore or lodash.

   https://github.com/google/lovefield

Lovefield is a relational query engine written in pure JavaScript. It also provides help with persisting data on the browser side, e.g. using IndexedDB to store data locally. It provides SQL-like syntax and works cross-browser (currently supporting Chrome 37+, Firefox 31+, IE 10+, and Safari 5.1+...

---

2. Another interesting recent entry in this space called jinqJs.

   http://www.jinqjs.com/

   Briefly reviewing the examples, it looks promising, and the API document appears to be well written.

---

function isChild(row) {
  return (row.Age < 18 ? 'Yes' : 'No');
}

var people = [
  {Name: 'Jane', Age: 20, Location: 'Smithtown'},
  {Name: 'Ken', Age: 57, Location: 'Islip'},
  {Name: 'Tom', Age: 10, Location: 'Islip'}
];

var result = new jinqJs()
  .from(people)
  .orderBy('Age')
  .select([{field: 'Name'}, 
     {field: 'Age', text: 'Your Age'}, 
     {text: 'Is Child', value: isChild}]);

---

jinqJs is a small, simple, lightweight and extensible javaScript library that has no dependencies. jinqJs provides a simple way to perform SQL like queries on javaScript arrays, collections and web services that return a JSON response. jinqJs is similar to Microsoft's Lambda expression for .Net, and it provides similar capabilities to query collections using a SQL like syntax and predicate functionality. jinqJs’s purpose is to provide a SQL like experience to programmers familiar with LINQ queries.

Answer 3

Another way to look at this would be to use mongoDB You can store your JSON in mongo and then query it via the mongodb query syntax.

Answer 4

ObjectPath is simple and ligthweigth query language for JSON documents of complex or unknown structure. It's similar to XPath or JSONPath, but much more powerful thanks to embedded arithmetic calculations, comparison mechanisms and built-in functions.

Python version is mature and used in production. JS is still in beta.

Probably in the near future we will provide a full-fledged Javascript version. We also want to develop it further, so that it could serve as a simpler alternative to Mongo queries.

Answer 5

The built-in array.filter() method makes most of these so-called javascript query libraries obsolete

You can put as many conditions inside the delegate as you can imagine: simple comparison, startsWith, etc. I haven't tested but you could probably nest filters too for querying inner collections.

Answer 6

Sure, how about:

• JaQL
• JsonPath.
• Json Query

They all seem to be a bit work in progress, but work to some degree. They are also similar to XPath and XQuery conceptually; even though XML and JSON have different conceptual models (hierarchic vs object/struct).

EDIT Sep-2015: Actually there is now JSON Pointer standard that allows very simple and efficient traversal of JSON content. It is not only formally specified, but also supported by many JSON libraries. So I would call it actual real useful standard, although due to its limited expressiveness it may or may not be considered Query Language per se.