JSON Query Languages: JSONPath, jq, JSONata, and SQL

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 19, 2025

Four languages dominate JSON querying: JSONPath (read-only path expressions, RFC 9535), jq (transformation and filtering CLI), JSONata (XPath-inspired with transformation, used in Node-RED and AWS), and SQL (via JSON functions in PostgreSQL, MySQL, and SQLite). JSONPath is supported natively in 15+ languages and AWS Step Functions; jq handles complex transformations that JSONPath cannot. JSONata processes 10,000 records in approximately 50 ms on a 2024 MacBook and supports grouping, sorting, and aggregation that jq requires multiple pipes to achieve. This guide compares all four — syntax, transformation power, language support, performance, and when to choose each — with a decision matrix included.

1. JSONPath (RFC 9535)

JSONPath selects nodes from a JSON document using path expressions rooted at $. RFC 9535, published by the IETF in February 2024, is the first formal standard for JSONPath, resolving ambiguities that caused inconsistent behavior across libraries for over 15 years.

Core syntax elements:

$                         — root of the document
$.store.books             — dot notation property access
$['store']['books']       — bracket notation (required for keys with spaces)
$.store.books[0]          — array index (zero-based)
$.store.books[-1]         — last element (RFC 9535 addition)
$.store.books[0,2]        — union: index 0 and index 2
$.store.books[1:3]        — slice: index 1 and 2 (stop is exclusive)
$.store.books[*]          — wildcard: all array elements
$..title                  — recursive descent: all "title" at any depth
$.store.books[?(@.price < 13)]   — filter expression

JSONPath always returns a nodelist — a list of matched values, possibly empty. A missing path never throws an error per RFC 9535, making it safe in pipelines where keys may be absent. Filter expressions use @ to refer to the current node being tested inside[?( )]: $.books[?(@.author.name == "Tolkien")] selects books whose nested author.name equals the target string. Libraries with confirmed RFC 9535 support include jsonpath-ng (Python), jsonpath-plus 9+ (JavaScript), nimma (Node.js), and Jayway JsonPath 2.9+ (Java).

JSONPath is the query language of choice for AWS Step Functions (InputPath, OutputPath, ResultPath), Kubernetes kubectl output formatting, and Grafana JSON data source queries. Its read-only nature makes it safe to store as a string in a database or pass as a configuration value — a JSONPath expression cannot mutate data. For more, see the JSONPath cheat sheet.

2. jq: Filter Pipelines and Transformation

jq is a lightweight, C-based command-line JSON processor that functions as a full programming language. Filters are chained with |; each filter receives input from the previous and passes output to the next. The jq binary is available on macOS via Homebrew (brew install jq), on Linux via apt/yum/dnf, and on Windows via winget.

.                          — identity: pass input unchanged
.foo                       — property access
.foo.bar                   — nested access
.foo?                      — optional: no error if .foo is missing
.foo // "default"          — alternative: use "default" if .foo is null/false
.books[]                   — iterate array (emit each element)
.books | length            — array or string length
.books | map(.title)       — transform each element, collect into array
select(.price > 10)        — keep elements where condition is true
{title, price}             — build new object (shorthand for {title: .title, price: .price})
{name: .title, p: .price}  — build object with renamed keys
[.books[].price] | add     — sum all prices
.books | group_by(.inStock)    — group array by field
.books | sort_by(.price)       — sort array by field
@base64                    — encode as base64
@json                      — convert value to JSON string
reduce .[] as $x (0; . + $x)  — fold/accumulate

jq also supports def for reusable functions, if/then/else/end conditionals, try/catch error handling, label-break for early exit, and --stream for parsing large files without loading them fully into memory. The update operator (.foo |= . + 1) modifies part of a structure while leaving the rest untouched. See jq filter examples for a cookbook of common one-liners.

3. JSONata: XPath-Inspired Transformation

JSONata was created by IBM and is the native expression language of Node-RED, IBM App Connect, and AWS EventBridge. It uses an XPath-inspired dot-notation that implicitly iterates arrays, making common paths concise. The jsonata npm package is a pure JavaScript library with no native dependencies.

// Installation
npm install jsonata

// Basic usage
const jsonata = require('jsonata');
const expr = jsonata('Account.Order.Product.Price');
const result = await expr.evaluate(data);   // returns array of all prices

// Arithmetic — implicit array iteration
Account.Order.Product.Price * 0.9           // 10% discount on each

// Aggregation
$sum(Account.Order.Product.Price)           // total of all prices
$count(Account.Order)                       // number of orders
$max(Account.Order.Product.Price)           // highest price

// Grouping (equivalent to SQL GROUP BY)
Account.Order{ Category: $sum(Product.Price) }

// Conditional
Account.Order.Product.(Price > 50 ? "premium" : "standard")

// String operations
$uppercase(Account.Name)
$join(Account.Order.Product.Name, ", ")

JSONata processes 10,000 records in approximately 50 ms in a Node.js process on modern hardware — fast enough for real-time API transformations. Its aggregation and grouping capabilities make it the most SQL-like of the JSON-native languages. AWS EventBridge uses JSONata in event patterns and transformer rules; if you are building event-driven pipelines on AWS, JSONata is already part of the toolchain.

4. SQL JSON Functions

All major SQL databases now support querying JSON stored in columns, letting you filter and extract without pulling data into application memory. Syntax varies by database:

PostgreSQL's jsonb type is the most powerful: it stores JSON in a binary format, supports GIN indexing for fast containment and path queries, and implements RFC 9535-style JSONPath via jsonb_path_query(). For repeated queries on the same dataset, a GIN-indexed jsonb column can outperform all three JSON-native query languages because the index avoids full document scans. See the PostgreSQL JSON guide for indexing strategies.

5. Side-by-Side Comparison

6. Decision Matrix

Use this matrix to pick the right tool for your scenario. Start with the environment constraint (CLI vs. in-process vs. database), then factor in whether you need transformation or just selection.

7. Same Query in All Four Languages

Given an orders dataset, these examples show the same three tasks — filter orders over $100, group by category, and compute the total — expressed in each language.

Sample data:

{
  "orders": [
    { "id": 1, "category": "electronics", "amount": 149.99, "status": "paid" },
    { "id": 2, "category": "books",       "amount": 24.95,  "status": "paid" },
    { "id": 3, "category": "electronics", "amount": 89.00,  "status": "pending" },
    { "id": 4, "category": "books",       "amount": 119.50, "status": "paid" }
  ]
}

Task 1: Filter orders over $100

# JSONPath — returns matching order objects
$.orders[?(@.amount > 100)]

# jq — returns matching order objects
.orders[] | select(.amount > 100)

# JSONata — returns array of matching orders
orders[amount > 100]

# SQL (PostgreSQL jsonb)
SELECT elem
FROM orders, jsonb_array_elements(data->'orders') AS elem
WHERE (elem->>'amount')::numeric > 100;

Task 2: Group by category

# JSONPath — NOT SUPPORTED (read-only)

# jq
[.orders[] | select(.amount > 100)]
  | group_by(.category)
  | map({ category: .[0].category, orders: . })

# JSONata
orders[amount > 100]{ category: $ }

# SQL (PostgreSQL)
SELECT data->>'category' AS category,
       jsonb_agg(data) AS orders
FROM (
  SELECT jsonb_array_elements(data->'orders') AS data
  FROM orders_table
) sub
WHERE (data->>'amount')::numeric > 100
GROUP BY data->>'category';

Task 3: Compute total amount per category

# JSONPath — NOT SUPPORTED

# jq
[.orders[] | select(.amount > 100)]
  | group_by(.category)
  | map({
      category: .[0].category,
      total: ([.[].amount] | add)
    })

# JSONata
orders[amount > 100]{ category: $sum(amount) }

# SQL (PostgreSQL)
SELECT data->>'category' AS category,
       SUM((data->>'amount')::numeric) AS total
FROM (
  SELECT jsonb_array_elements(data->'orders') AS data
  FROM orders_table
) sub
WHERE (data->>'amount')::numeric > 100
GROUP BY data->>'category';

The examples illustrate why JSONPath is excluded from aggregation tasks — its read-only design has no aggregation primitives. jq achieves the result through pipeline composition; JSONata achieves it with concise built-in syntax; SQL achieves it with standard relational clauses. For a deeper comparison of jq and JSONPath syntax, see the jq vs JSONPath guide.