10 Best JSON query languages

| 9 min read

In the article “4 handy categories of JSON tools” we explored different types of tools and when to use them. In this article we will zoom in on suitable tools to query JSON data. We will discuss 10 different JSON query languages, and you will learn how to create a JSON query.

The languages are not numbered and not ordered from best to worst, since there is no best or worst: which language is most suitable depends on your use case. You have to recon with the following points:

  • Use case:
    • Do you have to manually process and analyze a JSON document once? You can use a visual, web based application.
    • Do you have to automatically process JSON files repeatedly? Use a command line tool, shell script, or script language.
    • Do you have live updated data that regularly needs to be queried? Maybe best to use a database.
    • Do you work on an application that has to process JSON documents? Find a good JSON query library for the programming language to use in your application.
  • Environment: some languages can be used in a web application, others on the command line, others are database applications. or libraries to be used in a programming language.
  • Features: most languages support filtering, sorting, and picking properties out of objects. If you need more advanced features, you will have to look up whether your language of choice supports this.
  • Syntax: the different languages have a different syntax, and you may have a personal preference for one or the other.
  • Performance: the performance differs, and also the maximum supported amount of data can differ.

JMESPath

Website: https://jmespath.org/

JMESPath is a query language which has library implementations in many programming languages (JavaScript, Python, Java, Go, Rust, .Net, and others). There is a command line application, jp, available to use JMESPath on the command line. And JMESPath can be used as a query language in the visual web application JSON Editor Online. All of this makes JMESPath very versatile and usable in different contexts and environments.

JMESPath is powerful, mature, mainstream, and well documented. It is a good choice in many cases.

Example query: filtering a list with locations by the state “WA”, getting the name out of the object, then sorting these names and putting the result in a new object having the values joined in a comma separated string:

locations[?state == 'WA'].name | sort(@) | {WashingtonCities: join(', ', @)}

jq

Website: https://jqlang.github.io/jq/

Jq is a command line application to process JSON data. You can install it on your computer and use it on the command line or in shell scripts. It is a perfect fit for automated, repeated processing of JSON data files. There is an online environment where you can use jq here: https://www.devtoolsdaily.com/jq_playground/.

The application is mature, mainstream, and battle tested. It has a powerful and well documented query language. It can do filtering, sorting, pick fields, construct objects structures, apply mathematical operations, and more. Because it is such an extensive query language, it has a relatively steep learning curve. The features are well documented though, including examples, so this does not need to be an issue.

An example query sorting a list with countries by population and then returning the name of the country can look like:

.countries | sort_by(.population) | reverse | .[] | .name

JSONata

Website: https://jsonata.org/

A lightweight query and transformation language for JSON data. It is a JavaScript library, so it can be used in node.js and in the browser. There is a command line application for it named jfq. The query language is powerful, easy to learn, and well documented.

Example query, summing the price times quantity of a list of orders:

$sum(Account.Order.Product.(Price * Quantity))

JsonPath

Website: https://goessner.net/articles/JsonPath/

JsonPath is one of the older query languages for JSON, describing itself as “XPath for JSON”. It has implementations in JavaScript, Python, and Java, and more. It can be used in web applications https://www.jsonquerytool.com/, https://www.jstoolset.com/json-query, and https://jsonpath.com/ .

Example query, filtering a list with books by price and then returning the titles of the books:

$.store.book[?(@.price < 10)].title

MongoDB

Website: https://www.mongodb.com/

MongoDB is a powerful and well known NoSQL database. The database must be installed first on a computer or server, and then the data must be imported. MongoDB is often used as a database behind a web server, storing live data. It can process huge amounts of data, and it has client libraries in all popular languages to access the database. It comes with a command line application, and you can install a GUI such as MongoDBCompass to work with the data and visually create and execute queries.

Because the data is stored in a database, sharing data with someone is not as easy as sharing a JSON file.

The query language is extremely powerful. It is important though to create proper indexes to make querying performant.

Example query:

db.users.find({ 'name.family': 'Smith', age: { $gte: 18 } }).sort({ age: -1 })

JavaScript

Websites: https://www.javascript.com, https://nodejs.org/, https://lodash.com/

Scripting languages like Node.js (JavaScript) do not require a build step, you can write a file with some code and directly execute it via the command line.

When you are an experienced JavaScript developer, it is very easy to write a script that loads a JSON file, processes it with some JavaScript and/or Lodash functions, and output the result in another file. As soon as you need Lodash or another library, it requires setting up a small project and importing the libraries via npm, which gives some overhead.

A simple Node.js script to extract all titles of products having a price below 10 dollars can look like:

import { readFileSync, writeFileSync } from 'fs'

const input = JSON.parse(String(readFileSync('input.json')))
const output = input.filter((item) => item.price < 10).map((item) => item.title)

writeFileSync('output.json', JSON.stringify(output))

Note that the logic can be executed in the browser too, if you happen to have the data present in a browser. Also, you can open a JavaScript console in your browser, using it as a REPL: paste the data there in a variable, and execute the logic you need.

The web application JSON Editor Online also supports querying JSON data using JavaScript and Lodash via the transform modal, showing a real-time preview. A query can look like:

function query(data) {
  return _.chain(data)
    .filter((item) => item?.city == 'New York')
    .orderBy(['name'], ['asc'])
    .map((item) => item?.name)
    .value()
}

Python

Website: https://www.python.org/

Similarly to JavaScript, when you’re an experienced Python developer you can simply use Python to query JSON data. Python works nicely with JSON data.

Here an example which reads a file input.json, filters the items by city, and outputs the names into a file output.json:

import json

with open("input.json", "r") as read_file:
  input = json.load(read_file)

filtered = filter(lambda item: item['city'] == "New York", input)
mapped = map(lambda item: item['name'], filtered)
output = list(mapped)

with open("output.json", "w") as write_file:
  json.dump(output, write_file)

SQLite

Website: https://www.sqlite.org/

SQLite is a small, fast, and ubiquitous database. It claims to be the most used database engine in the world. It is easier to set up and maintain than MongoDB, and the data is easier to transfer. SQLite is an SQL database, but it has good support for JSON.

Example query:

SELECT json_extract(data, '$.name') AS name
FROM users
WHERE json_extract( data, '$.age' ) > 18
ORDER BY json_extract( data, '$.age' ) DESC;

Or using the -> operator:

SELECT data -> '$.name' AS name
FROM users
WHERE data -> '$.age' >= 18
ORDER BY data -> '$.age' DESC;

CouchDB

Website: https://couchdb.apache.org/

CouchDB is a NoSQL database, using unstructured JSON too, similar to MongoDB in that regard. CouchDB is accessible via an HTTP REST API, and comes with a browser based user interface built-in. CouchDB takes quite a different approach than MongoDB, for example regarding revisioning, distribution, replication, and eventual consistency.

In CouchDB you create views, which are executed once and automatically kept up to date. This way, repeatedly querying the same data is instant the result of the view is already there. To create a view, you can apply a map and reduce operation on the data. You write map and reduce operations in JavaScript, which gives you a lot of freedom. On the other hand, the requirement of writing a query in terms of a map and reduce operation has its limits and can be challenging. Here is an example of a map operation on a document: it reads an object with product names as key and and prices as value, and outputs all prices.

function(doc) {
  var shop, price, value
  if (doc.item && doc.prices) {
    for (shop in doc.prices) {
      price = doc.prices[shop]
      value = [doc.item, shop]
      emit(price, value)
    }
  }
}

For some time, CouchDB also offers a query language called Mango, similar to MongoDB. Such a query looks like:

{
  "selector": {
    "year": {
      "$lt": 1990,
      "$gte": 1980
    }
  },
  "fields": ["_id", "_rev", "title", "content", "year", "author"],
  "sort": [],
  "limit": 10,
  "skip": 0
}

GraphQL

Website: https://graphql.org/

Now, this one is a bit off in this list with JSON query languages GraphQL is not a query language to query JSON perse: it is “a query language for APIs and a runtime for fulfilling those queries with your existing data”. If you have a backend and a database with data, you can implement a GraphQL API to expose your data via a REST API and make it queryable for other parties. You have to implement all query actions that you want to support yourself in your backend though. GraphQL can be a good solution if you want to open up your data and give consumers freedom in querying the data in various ways.

A query finding friends located in a specific city can look like:

query GetFriendsByCity {
  friends(city: "new York") {
    name
    age
    city
  }
}

Others

There are many more libraries and applications. Some others worth mentioning:

Conclusion

Since JSON is such an ambiguous data format, there is a rich ecosystem of libraries and tools available to work with JSON. In this article we discussed 10 different languages to query JSON data. These are all high quality solutions, and you can pick what best suits your use case.

Update 2024-09-27: JSON Query

A new query language named JSON Query has been developed with some interesting features. It’s lightweight, and offers both a human friendly syntax and a machine friendly JSON syntax. The intermediate JSON format (an AST) makes it easy to develop for example a graphical user interface on top of it, develop a command line application for it, or implement a query engine for in a different environment. You can read all about it in the article “JSON Query: a small, flexible, and expandable JSON query language”.

Here an example of the text format of JSON Query:

filter(.city == "New York") | sort(.name) | map(.name)

And here the same query in the intermediate JSON format, purely consisting of composed function calls [name, arg1, arg2, ...]:

[
  "pipe",
  ["filter", ["eq", ["get", "city"], "New York"]],
  ["sort", ["get", "name"]],
  ["map", ["get", "name"]]
]

Contents

Recent posts

Categories

About