Is there a query language for JSON?

Question

Sure, how about:

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

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.

Update: XQuery 3.1 can query either XML or JSON - or both together. And XPath 3.1 can too.

The list is growing:

• JSONiq (based on XQuery)
• UNQL (like SQL)
• JaQL (functional)
• JsonPath (XPath-like)
• Json Query (sort of XPath-like)
• GraphQL (template-based, typed)

JMESPath works really quite easy and well: http://jmespath.org/. It has a thorough specification, and libraries for multiple languages. It is being used by Amazon in the AWS command line interface, so it’s got to be quite stable.

Syntax Examples:

// Select a single item
people[1].firstName

// Select a slice of an array
people[0:5]

// Select all the first names
people[*].firstName

// Select all first names based on search term
people[?state=='VA'].firstName

// Count how many people are over 35
length(people[?age>`35`])

// Select only the name and age of people over 35
people[?age>`35`].{name: name, age: age}

// Join expressions together to sort and join elements into a string
people[?state == 'WA'].name | sort(@) | join(', ', @)

There are plenty more live examples you can play with in the docs.

jq is a JSON query language, mainly intended for the command-line but with bindings to a wide range of programming languages (Java, node.js, php, ...) and even available in the browser via jq-web. The C-based implementation of jq is generally known as "jq", and the Go-based version as "gojq".

Here are some illustrations based on the original question, which gave this JSON as an example:

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

SUM(X) WHERE Y > 0 (would equate to 7)

map(select(.y > 0)) | add

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

map(.y > 0)

jq syntax extends JSON syntax

Every JSON expression is a valid jq expression, and expressions such as [1, (1+1)] and {"a": (1+1)}` illustrate how jq extends JSON syntax.

A more useful example is the jq expression:

{a,b}

which, given the JSON value {"a":1, "b":2, "c": 3}, evaluates to {"a":1, "b":2}.

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.

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.