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: Thanks for the suggestions folks. I appreciate it. 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

JSON = Javascript Object Notation, why not just use Javascript? You said small datasets and it appears you just want basic data processing.

Answer 2

I'll second the notion of just using your own javascript, but for something a bit more sophisticated you might look at dojo data. Haven't used it but it looks like it gives you roughly the kind of query interface you're looking for.

Answer 3

The only thing I can say is that you are using JSON outside of javascript, you should treat it as you would an array. Arrays don't have a query language (that I know of) but they have a lot of methods/functions that can be used on them to do things like summing and the like. In PHP, your example would just be something like:

$json = json_decode([{"x": 2, "y", 0}, {"x": 3, "y", 1}, {"x": 4, "y": 1}]);

// Two lines ain't bad...
$x_sum = 0;
foreach($json as $element) { $x_sum += $element['x']; }

If interpreting JSON in javascript, just use it for it is... an object/array (they are the same thing in JavaScript for the most part). I'm not convinced that the bloat of a query language would be beneficial.

Answer 4

I too don't really see a need for a query language for JSON. JSON is just a data structure and won't be guaranteed any set format. Searching for what you want in the data is going to be very specific to the data itself and you'll probably be just as well off writing custom query code. Also, the data returned is going to be specific to the data and your application and I can't see how a standardized query language would do a good job dealing with such a variety of types of data.

As a Perl programmer I don't think I've ever heard of anyone wanting a query language to search through a perl data structure. Data's always searched with custom code so that it perfectly fits the data structure and what the application needs to pull from it.

That said, I did vote up your question because I would be at least interested in seeing how a json query language would work and be useful.

Answer 5

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).

Answer 6

The current Jaql implementation targets large data processing using a Hadoop cluster, so it might be more than you need. However, it runs easily without a Hadoop cluster (but still requires the Hadoop code and its dependencies to get compiled, which are mostly included). A small implementation of Jaql that could be embedded in Javascript and the a browser would be a great addition to the project.

Your examples above are easily written in jaql:

$data = [{"x": 2, "y": 0}, {"x": 3, "y": 1}, {"x": 4, "y": 1}];

$data -> filter $.y > 0 -> transform $.x -> sum(); // 7

$data -> filter $.y > 0 -> transform $.x; // [3,4]

Of course, there's much more too. For example:

// Compute multiple aggregates and change nesting structure:
$data -> group by $y = $.y into { $y, s:sum($[*].x), n:count($), xs:$[*].x}; 
    // [{ "y": 0, "s": 2, "n": 1, "xs": [2]   },
    //  { "y": 1, "s": 7, "n": 2, "xs": [3,4] }]

// Join multiple data sets:
$more = [{ "y": 0, "z": 5 }, { "y": 1, "z": 6 }];
join $data, $more where $data.y == $more.y into {$data, $more};
    // [{ "data": { "x": 2, "y": 0 }, "more": { "y": 0, "z": 5 }},
    //  { "data": { "x": 3, "y": 1 }, "more": { "y": 1, "z": 6 }},
    //  { "data": { "x": 4, "y": 1 }, "more": { "y": 1, "z": 6 }}]

Jaql can be downloaded/discussed at http://code.google.com/p/jaql/

Answer 7

If you are using .NET then Json.NET supports LINQ queries over the top of JSON. This post has some examples. It supports filtering, mapping, grouping, etc.

Answer 8

I'd recommend my project I'm working on called jLinq. I'm looking for feedback so I'd be interested in hearing what you think.

If lets you write queries similar to how you would in LINQ...

var results = jLinq.from(records.users)

    //you can join records
    .join(records.locations, "location", "locationId", "id")

    //write queries on the data
    .startsWith("firstname", "j")
    .or("k") //automatically remembers field and command names

    //even query joined items
    .equals("location.state", "TX")

    //and even do custom selections
    .select(function(rec) {
        return {
            fullname : rec.firstname + " " + rec.lastname,
            city : rec.location.city,
            ageInTenYears : (rec.age + 10)
        };
    });

It's fully extensible too!

The documentation is still in progress, but you can still try it online.