Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

Type a name or a two-letter code. Use the up and down arrow keys to move through the countries, Enter to choose one and Escape to close.

Data Formats (JSON, YAML, TOML, XML, CSV)  Module 4 – Querying JSON: JSONPath, jq, JMESPath and SQL

jq, JMESPath and SQL JSON paths compared

Reshape JSON with jq, query it with JMESPath and with the JSON paths of SQLite and PostgreSQL, then choose between them and translate one into another.

  • Intermediate
  • 25 minutes
  • Examples run with Node.js 24.21.0, quickjs 0.32.0, Python 3.14.8, Pyodide 314.0.7, Ruby 4.0.7 and sql.js 1.14.2
  • By MySmartCoPilot
Free lesson, Pro answers: 25 minutes
Free: the whole lesson. With a pass: the model answers to its 3 interview questions.

What you will learn

  • Transform JSON with jq filters, including map, select and group_by
  • Write JMESPath expressions of the kind the AWS CLI's --query option takes
  • Query JSON inside SQLite and PostgreSQL with their own path syntaxes
  • Choose the right query language for a job and translate queries between them

Before you start

What you’ll learn · Before you start

What you will learn

  • Transform JSON with jq filters, including map, select and group_by
  • Write JMESPath expressions of the kind the AWS CLI's --query option takes
  • Query JSON inside SQLite and PostgreSQL with their own path syntaxes
  • Choose the right query language for a job and translate queries between them

Before you start

On this page

The bookshop’s five branches sold books worth 434800 paise in 7 paid orders, and the manager wants the total for each city. JSONPath can select the paid orders but cannot add anything up. jq answers with one program, JMESPath only city by city, and SQL with a GROUP BY, because each of these languages was made for a different job.

You will reshape JSON with jq’s filters, write the JMESPath expressions that a command-line tool’s --query option takes, and query JSON stored in SQLite and PostgreSQL with their own path syntaxes. Then you will choose between the four for a job, and translate a query from one to another without falling into the traps that look alike.

Four ways to query JSON, four different jobs

The four languages answer different questions. JSONPath, which the last two lessons taught, selects nodes and returns them as they are. JMESPath also selects, but builds new lists and objects from what it finds, and has functions such as sum(). jq is a whole programming language whose programs are pipelines of filters, so it can select, reshape, group and compute. The JSON functions of SQL databases read values out of documents with paths of their own, and leave grouping and totals to SQL. Answer two questions about a job of your own, and the table marks the ways that fit:

Interactive · answer and compare Which way of querying JSON fits your job? Answer two questions about the job. The table marks the languages that fit, and says why it rules the others out.
Where does the query have to run?
What should the answer be?

Answer the questions above: the table then marks what fits your case and says why the rest does not.

Five ways to query JSON against what each can do with the answer and where it runs
Language Select values Build a new shape Group and total Written down in
JSONPath (RFC 9535) Runs in: a library inside your program Good nodelists, with a normalized path for every value Poor returns the nodes as they are; any new shape is built in code Poor no sums or groups: totals.py added them in Python Good RFC 9535, with a public compliance test suite
JMESPath Runs in: a library, or the --query option of a CLI Good projections and filters such as orders[?status == 'paid'] Good multiselect lists and hashes build new objects Mixed sum() and max_by(), but no group_by: one sum per city, named in advance Good the jmespath.org specification
jq Runs in: a shell command, or a library Good select(), paths and .. reach any value Good builds any JSON: objects, arrays and strings Good group_by and add gave the five city totals in one program Mixed one program, defined by its manual
SQLite JSON functions Runs in: SQL queries in SQLite Good ->>, json_extract() and json_each(), with paths such as '$.items[#-1]' Mixed json_object() and json_group_array(), written in SQL Good GROUP BY on the values that the paths read Mixed SQLite's documentation of its own path syntax
PostgreSQL jsonpath Runs in: SQL queries in PostgreSQL Good @?, jsonb_path_query() and the arrow operators Mixed jsonb_build_object() and jsonb_agg(), written in SQL Good GROUP BY, as the totals of the session showed Good the SQL/JSON path language, with a few extensions
How the answers decide
  • “Where does the query have to run?” Inside my own program: rules out SQLite JSON functions and PostgreSQL jsonpath. SQL paths run inside a database query; a program would need a database just to read its own JSON.
  • “Where does the query have to run?” In a shell script or on a command line: rules out JSONPath (RFC 9535), SQLite JSON functions and PostgreSQL jsonpath. jq and the --query option of a CLI take an expression on the command line; JSONPath and SQL need a program or a database around them.
  • “Where does the query have to run?” Inside the database that stores the JSON: rules out JSONPath (RFC 9535), JMESPath and jq. Only the database's own functions run inside its queries, next to the indexes and the rest of the data.
  • “What should the answer be?” A new shape built from the document: rules out JSONPath (RFC 9535) and adds a caveat to SQLite JSON functions and PostgreSQL jsonpath. JSONPath returns the nodes as they are. SQL can build JSON, but with functions such as json_object() rather than in its paths.
  • “What should the answer be?” Groups and totals: rules out JSONPath (RFC 9535) and adds a caveat to JMESPath. JSONPath cannot add values up, and JMESPath can sum a list but not group one: each city needs its own expression.

In short: JSONPath for selecting values inside a program, JMESPath for the --query option of a command-line tool, jq for shell scripts and for any reshaping, and the database’s own paths for JSON that already lives in a database. The rest of the lesson runs each of them on the same 8 orders.

Checkpoint

Check: Choosing a query language for a job

  1. Question 1 of 2 A shell script must read a JSON file and print the id of every failed job, one per line. Which fits best?

    Choose one answer.

    Show the answer: Question 1 of 2

    Answer: jq, with -r and a select() filter

    jq -r '.jobs[] | select(.status == "failed") | .id' jobs.json is the whole job. The table rules out JSONPath and SQL for the shell.

  2. Question 2 of 2 Orders are stored as jsonb in PostgreSQL, and a report needs the total per city. Which fits best?

    Choose one answer.

    Show the answer: Question 2 of 2

    Answer: PostgreSQL's own operators and paths, with GROUP BY

    When the JSON lives in a database, its own paths read the values and SQL groups them, with the database's indexes at hand.

jq is a programming language of filters

A jq program is a jq filter: A jq program, or a part of one: it takes one JSON input and produces zero, one or many outputs. Filters join with | into a pipeline, and each passes every output it produces to the next.: it takes one JSON input and produces zero, one or many outputs. | joins filters into a pipeline, passing every output of the left one to the right one, so .orders[] produces the 8 orders one by one, and select(.amount_paise > 60000) passes on only the orders it is true for. The jq 1.8 manual defines map(f) as [.[] | f], and group_by(.city) as grouping the elements of an array that have the same .city into separate arrays. This session runs nine programs with jq 1.8.2, all but one of them on the orders:

Nine jq programs on the bookshop's orders Python · jq/jq_session.py
from session import run, version

# jq programs on the bookshop's orders: select, totals, a pipeline that groups, a variable, the .. of jq, what jq
# does with a number literal that it computes with and one that it passes on, and two traps for readers of JSONPath.
version()
run(
    r"""
jq -r '.orders[] | select(.amount_paise > 60000) | .id' orders.json
jq '[.orders[] | select(.status == "paid") | .amount_paise] | add' orders.json
jq -c '[.orders[] | select(.status == "paid")] | group_by(.city) | map({city: .[0].city, paise: (map(.amount_paise) | add)})' orders.json
jq -r --arg city Kochi '.orders[] | select(.city == $city) | .id' orders.json
jq -c '[.. | .sku? | strings] | unique' orders.json
jq -c '.orders | map({id, books: (.items | map(.qty) | add)}) | .[:3]' orders.json
echo '{"ref": 12345678901234567890, "price": 1.10}' | jq -c '., .ref + 0, .price * 1'
jq -c '[.orders[] | select(.coupon == null) | .id] | length' orders.json
jq '.orders[0].items.sku' orders.json
""",
    expect_failure=("jq '.orders[0].items.sku' orders.json",),
)

Output

# jq-1.8.2
$ jq -r '.orders[] | select(.amount_paise > 60000) | .id' orders.json
BK-20001
BK-20005
BK-20007
$ jq '[.orders[] | select(.status == "paid") | .amount_paise] | add' orders.json
434800
$ jq -c '[.orders[] | select(.status == "paid")] | group_by(.city) | map({city: .[0].city, paise: (map(.amount_paise) | add)})' orders.json
[{"city":"Guwahati","paise":69800},{"city":"Indore","paise":59700},{"city":"Kochi","paise":72400},{"city":"Mysuru","paise":108300},{"city":"Pune","paise":124600}]
$ jq -r --arg city Kochi '.orders[] | select(.city == $city) | .id' orders.json
BK-20002
BK-20006
$ jq -c '[.. | .sku? | strings] | unique' orders.json
["BK-101","BK-205","BK-233","BK-310"]
$ jq -c '.orders | map({id, books: (.items | map(.qty) | add)}) | .[:3]' orders.json
[{"id":"BK-20001","books":3},{"id":"BK-20002","books":1},{"id":"BK-20003","books":1}]
$ echo '{"ref": 12345678901234567890, "price": 1.10}' | jq -c '., .ref + 0, .price * 1'
{"ref":12345678901234567890,"price":1.10}
12345678901234567000
1.1
$ jq -c '[.orders[] | select(.coupon == null) | .id] | length' orders.json
8
$ jq '.orders[0].items.sku' orders.json
jq: error (at orders.json:20): Cannot index array with string ("sku")
[exit status 5]

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 jq_session.py

The first program printed the ids of the three orders above 60000 paise, raw, because -r writes strings without their quotes. The second collected the paid amounts into an array with [ … ] and added them: 434800. The third is the city totals, which the next figure takes apart. --arg city Kochi passes a value in as the variable $city, so a shell script never pastes text into the program, and .. is jq’s recursive descent: with .sku? and strings it collected every sku anywhere in the document. Step through the city totals, one filter at a time:

Interactive · stage by stage The city totals, one jq filter at a time Step through the pipeline that turned 8 orders into 5 city totals. Each stage is one filter; | hands every output of one stage to the next.
The city totals, one jq filter at a time, step 6 of 6. Showing .orders[], select(.status == "paid"), [ … ], group_by(.city), map({city, paise})..orders[]8 outputs: one per orderselect(.status == "paid")7 outputs[ … ]1 output: an array of 7group_by(.city)5 arrays, sorted by citymap({city, paise})5 objects with sums

The drawing shows the whole scene; its 6 steps are listed below.

Every step as text
  1. Every order, one at a time. .orders[] produces each element of the array as a separate output: eight outputs, one per order, not one array.
  2. select() keeps the paid ones. select(f) passes its input on when f is true and produces nothing otherwise. BK-20003 was refunded, so seven orders go on.
  3. Brackets collect the outputs. Writing the first two filters inside [ ] collects their seven outputs into one array, which the rest of the pipeline works on as a whole.
  4. group_by() makes the groups. group_by(.city) sorts the array by city and splits it into arrays of orders with the same city: Guwahati, Indore, Kochi, Mysuru and Pune.
  5. map() builds one total per city. map(f) runs f on each group: .[0].city takes the city of its first order, and map(.amount_paise) | add sums the group, 124600 paise for Pune.
  6. One program, the whole answer. The output is the array of five totals that the session printed. jq read the orders, filtered, grouped and added them without any other language.

jq keeps numbers as their text until it computes with them. The manual says jq “will attempt to maintain the original decimal precision of number literals”, and the run shows how far that goes: 12345678901234567890 and 1.10 came out exactly as written, but .ref + 0 printed 12345678901234567000 and .price * 1 printed 1.1, because arithmetic works on IEEE 754 double precision values. Pass ids through jq untouched, or send them as strings.

Checkpoint

Check: jq filters and pipelines

  1. Question 1 of 2 What does the jq filter select(f) do with its input?

    Choose one answer.

    Show the answer: Question 1 of 2

    Answer: It passes the input on when f is true, and produces nothing otherwise

    In the words of the jq manual, select(f) "produces its input unchanged if f returns true for that input, and produces no output otherwise."

  2. Question 2 of 2 What does jq 1.8.2 print for .ref + 0 when the input is {"ref": 12345678901234567890}?

    Choose one answer.

    Show the answer: Question 2 of 2

    Answer: 12345678901234567000

    jq keeps the text of a number literal only until it computes with it. Send ids as strings, or pass them through untouched.

JMESPath projects lists into new shapes

JMESPath is the expression language that the AWS CLI’s --query option takes. Its user guide calls this client-side filtering: the whole response reaches your machine first, and the expression trims and reshapes it there before anything is printed. The expressions read like paths: orders[0].id is the id of the first order. The power is in projection: In JMESPath, an expression such as orders[*], orders[] or orders[?…] that applies the rest of the expression to every element of a list and leaves out the elements whose result is null.: orders[*], orders[] and orders[?…] apply the rest of the expression to every element, and the specification says that a result that is null is left out of the list. The programs of this section read these orders:

The bookshop's orders JSON · python/orders.json
{
  "orders": [
    { "id": "BK-20001", "city": "Pune", "amount_paise": 89700, "status": "paid",
      "items": [{ "sku": "BK-101", "qty": 2 }, { "sku": "BK-205", "qty": 1 }] },
    { "id": "BK-20002", "city": "Kochi", "amount_paise": 52500, "status": "paid", "coupon": null,
      "items": [{ "sku": "BK-233", "qty": 1 }] },
    { "id": "BK-20003", "city": "Pune", "amount_paise": 27900, "status": "refunded",
      "items": [{ "sku": "BK-310", "qty": 1 }] },
    { "id": "BK-20004", "city": "Indore", "amount_paise": 59700, "status": "paid",
      "items": [{ "sku": "BK-205", "qty": 3 }] },
    { "id": "BK-20005", "city": "Guwahati", "amount_paise": 69800, "status": "paid",
      "items": [{ "sku": "BK-101", "qty": 2 }] },
    { "id": "BK-20006", "city": "Kochi", "amount_paise": 19900, "status": "paid",
      "items": [{ "sku": "BK-205", "qty": 1 }] },
    { "id": "BK-20007", "city": "Mysuru", "amount_paise": 108300, "status": "paid",
      "items": [{ "sku": "BK-310", "qty": 2 }, { "sku": "BK-233", "qty": 1 }] },
    { "id": "BK-20008", "city": "Pune", "amount_paise": 34900, "status": "paid",
      "items": [{ "sku": "BK-101", "qty": 1 }] }
  ]
}

This program runs eleven expressions with a small interpreter of the specification, nine on the orders and two on output shaped like that of aws ec2 describe-instances:

Eleven JMESPath expressions and what each gives Python · python/jmespath_demo.py
"""JMESPath expressions on the orders and on output shaped like a cloud command-line tool's, with what each gives."""
import json

from jmespath_mini import search

with open("orders.json", encoding="utf-8") as f:
    orders = json.load(f)
with open("instances.json", encoding="utf-8") as f:
    instances = json.load(f)

ON_ORDERS = [
    "orders[?status == 'paid'].id",
    "orders[?amount_paise > `60000`].{id: id, city: city}",
    "sum(orders[?status == 'paid'].amount_paise)",
    "orders[].items[].sku",
    "orders[:2].items[*].sku",
    "max_by(orders, &amount_paise).id",
    "orders[?status == 'paid'].amount_paise | [0]",
    "orders[?status == 'paid'].amount_paise[0]",
    "{Pune: sum(orders[?city == 'Pune' && status == 'paid'].amount_paise), Kochi: sum(orders[?city == 'Kochi' && status == 'paid'].amount_paise)}",
]
ON_INSTANCES = [
    "Reservations[].Instances[?State.Name == 'running'].InstanceId[]",
    "Reservations[].Instances[].{id: InstanceId, name: Tags[?Key == 'Name'].Value | [0]}",
]

for document, expressions in ((orders, ON_ORDERS), (instances, ON_INSTANCES)):
    for expression in expressions:
        print(expression)
        print("   ", json.dumps(search(expression, document), ensure_ascii=False))

Output

orders[?status == 'paid'].id
    ["BK-20001", "BK-20002", "BK-20004", "BK-20005", "BK-20006", "BK-20007", "BK-20008"]
orders[?amount_paise > `60000`].{id: id, city: city}
    [{"id": "BK-20001", "city": "Pune"}, {"id": "BK-20005", "city": "Guwahati"}, {"id": "BK-20007", "city": "Mysuru"}]
sum(orders[?status == 'paid'].amount_paise)
    434800
orders[].items[].sku
    ["BK-101", "BK-205", "BK-233", "BK-310", "BK-205", "BK-101", "BK-205", "BK-310", "BK-233", "BK-101"]
orders[:2].items[*].sku
    [["BK-101", "BK-205"], ["BK-233"]]
max_by(orders, &amount_paise).id
    "BK-20007"
orders[?status == 'paid'].amount_paise | [0]
    89700
orders[?status == 'paid'].amount_paise[0]
    []
{Pune: sum(orders[?city == 'Pune' && status == 'paid'].amount_paise), Kochi: sum(orders[?city == 'Kochi' && status == 'paid'].amount_paise)}
    {"Pune": 124600, "Kochi": 72400}
Reservations[].Instances[?State.Name == 'running'].InstanceId[]
    ["i-0a11", "i-0b21"]
Reservations[].Instances[].{id: InstanceId, name: Tags[?Key == 'Name'].Value | [0]}
    [{"id": "i-0a11", "name": "shop-web-1"}, {"id": "i-0a12", "name": "shop-batch"}, {"id": "i-0b21", "name": null}]

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 jmespath_demo.py

Output shaped like a cloud CLI's list of servers JSON · python/instances.json
{
  "Reservations": [
    {
      "Instances": [
        { "InstanceId": "i-0a11", "InstanceType": "t3.micro", "State": { "Name": "running" },
          "Tags": [{ "Key": "Name", "Value": "shop-web-1" }, { "Key": "branch", "Value": "Pune" }] },
        { "InstanceId": "i-0a12", "InstanceType": "m5.large", "State": { "Name": "stopped" },
          "Tags": [{ "Key": "Name", "Value": "shop-batch" }] }
      ]
    },
    {
      "Instances": [
        { "InstanceId": "i-0b21", "InstanceType": "t3.micro", "State": { "Name": "running" }, "Tags": [] }
      ]
    }
  ]
}

orders[?status == 'paid'].id filtered and then projected, giving 7 ids. {id: id, city: city} is a multiselect hash: it built a new object for every order above 60000 paise. orders[].items[].sku flattened two levels into one list of 10 skus, while orders[:2].items[*].sku kept one list per order. sum() and max_by() are two of the specification’s 26 built-in functions, and &amount_paise hands max_by() an expression to compare by.

Two lines show the rule that trips most people. orders[?status == 'paid'].amount_paise | [0] gave 89700, the first amount, because a pipe stops the projection and [0] applies to the whole list. Without the pipe, amount_paise[0] is applied to each amount, a number, which gives null, and the nulls are left out: []. And there is no group_by: the totals for Pune and Kochi needed one sum() each, with the cities written into the expression. The same rules work on the command line. With the AWS CLI, the expression on instances.json would go after --query, in quotes, and the AWS documentation warns that with --output text the query runs once on each page of output, so an expression that ends in | [0] can print one id per page.

Checkpoint

Check: JMESPath projections and functions

  1. Question 1 of 2 Why did orders[?status == 'paid'].amount_paise[0] give [], while … .amount_paise | [0] gave 89700?

    Choose one answer.

    Show the answer: Question 1 of 2

    Answer: Without the pipe, [0] is applied to each amount, a number, which gives null, and nulls are dropped

    A projection applies the rest of the expression to every element and leaves out null results. A pipe ends the projection.

  2. Question 2 of 2 Which JMESPath expression gives a list of objects with only the id and the city of every order?

    Choose one answer.

    Show the answer: Question 2 of 2

    Answer: orders[*].{id: id, city: city}

    The demo built such objects for the orders above 60000 paise with orders[?amount_paise > 60000].{id: id, city: city}.

SQL reads JSON with paths of its own

SQLite and PostgreSQL store JSON in a column and read it with operators and paths inside SQL. SQLite’s paths start with $, take .name and [n], and count from the end with #, so $.items[#-1] is the last item. Since SQLite 3.38.0, -> returns the JSON text of what a path selects and ->> returns an SQL value: text, an integer, a real or NULL. This script runs in sql.js 1.14.2, the SQLite 3.49.1 that Run uses in your browser. Press Run, then change 'Pune' in the first query to another city and run it again:

SQLite's JSON functions on one order per row SQL · sqlite/json_paths.sql
-- The bookshop's orders, one JSON document per row, read with SQLite's JSON functions and its own paths.
CREATE TABLE orders (doc TEXT NOT NULL CHECK (json_valid(doc)));
INSERT INTO orders (doc) VALUES
  ('{"id": "BK-20001", "city": "Pune", "amount_paise": 89700, "status": "paid", "items": [{"sku": "BK-101", "qty": 2}, {"sku": "BK-205", "qty": 1}]}'),
  ('{"id": "BK-20002", "city": "Kochi", "amount_paise": 52500, "status": "paid", "coupon": null, "items": [{"sku": "BK-233", "qty": 1}]}'),
  ('{"id": "BK-20003", "city": "Pune", "amount_paise": 27900, "status": "refunded", "items": [{"sku": "BK-310", "qty": 1}]}'),
  ('{"id": "BK-20004", "city": "Indore", "amount_paise": 59700, "status": "paid", "items": [{"sku": "BK-205", "qty": 3}]}'),
  ('{"id": "BK-20005", "city": "Guwahati", "amount_paise": 69800, "status": "paid", "items": [{"sku": "BK-101", "qty": 2}]}'),
  ('{"id": "BK-20006", "city": "Kochi", "amount_paise": 19900, "status": "paid", "items": [{"sku": "BK-205", "qty": 1}]}'),
  ('{"id": "BK-20007", "city": "Mysuru", "amount_paise": 108300, "status": "paid", "items": [{"sku": "BK-310", "qty": 2}, {"sku": "BK-233", "qty": 1}]}'),
  ('{"id": "BK-20008", "city": "Pune", "amount_paise": 34900, "status": "paid", "items": [{"sku": "BK-101", "qty": 1}]}');

-- A path picks one value: ->> gives an SQL value, -> gives JSON text, and [#-1] is the last element.
SELECT doc ->> '$.id' AS id, doc ->> '$.items[0].sku' AS first_sku, doc -> '$.items[#-1]' AS last_item
FROM orders WHERE doc ->> '$.city' = 'Pune';

-- The same member through both arrows: JSON text keeps its quotes, the SQL value does not.
SELECT doc -> '$.city' AS arrow, doc ->> '$.city' AS double_arrow, typeof(doc ->> '$.amount_paise') AS amount_type
FROM orders LIMIT 1;

-- json_each turns an array into rows, one per element, so a WHERE can test every item.
SELECT doc ->> '$.id' AS id, item.value ->> '$.sku' AS sku, item.value ->> '$.qty' AS qty
FROM orders, json_each(orders.doc, '$.items') AS item
WHERE item.value ->> '$.qty' >= 2;

-- The totals of the paid orders per city, with GROUP BY doing what JSONPath cannot.
SELECT doc ->> '$.city' AS city, count(*) AS orders, sum(doc ->> '$.amount_paise') AS paise
FROM orders WHERE doc ->> '$.status' = 'paid'
GROUP BY city ORDER BY paise DESC;

-- ->> gives SQL NULL for a JSON null and for a member that is not there, and json_type() tells the two apart.
SELECT doc ->> '$.id' AS id, doc ->> '$.coupon' IS NULL AS arrow_is_null, json_type(doc, '$.coupon') AS coupon_type
FROM orders WHERE doc ->> '$.city' = 'Kochi';

-- json_tree() walks every level, like .. in JSONPath and jq: every distinct sku, wherever it is.
SELECT DISTINCT tree.atom AS sku
FROM orders, json_tree(orders.doc) AS tree
WHERE tree.key = 'sku' ORDER BY sku;

Output

┌──────────┬───────────┬──────────────────────────┐
│    id    │ first_sku │        last_item         │
├──────────┼───────────┼──────────────────────────┤
│ BK-20001 │ BK-101    │ {"sku":"BK-205","qty":1} │
│ BK-20003 │ BK-310    │ {"sku":"BK-310","qty":1} │
│ BK-20008 │ BK-101    │ {"sku":"BK-101","qty":1} │
└──────────┴───────────┴──────────────────────────┘
┌────────┬──────────────┬─────────────┐
│ arrow  │ double_arrow │ amount_type │
├────────┼──────────────┼─────────────┤
│ "Pune" │ Pune         │ integer     │
└────────┴──────────────┴─────────────┘
┌──────────┬────────┬─────┐
│    id    │  sku   │ qty │
├──────────┼────────┼─────┤
│ BK-20001 │ BK-101 │ 2   │
│ BK-20004 │ BK-205 │ 3   │
│ BK-20005 │ BK-101 │ 2   │
│ BK-20007 │ BK-310 │ 2   │
└──────────┴────────┴─────┘
┌──────────┬────────┬────────┐
│   city   │ orders │ paise  │
├──────────┼────────┼────────┤
│ Pune     │ 2      │ 124600 │
│ Mysuru   │ 1      │ 108300 │
│ Kochi    │ 2      │ 72400  │
│ Guwahati │ 1      │ 69800  │
│ Indore   │ 1      │ 59700  │
└──────────┴────────┴────────┘
┌──────────┬───────────────┬─────────────┐
│    id    │ arrow_is_null │ coupon_type │
├──────────┼───────────────┼─────────────┤
│ BK-20002 │ 1             │ null        │
│ BK-20006 │ 1             │             │
└──────────┴───────────────┴─────────────┘
┌────────┐
│  sku   │
├────────┤
│ BK-101 │
│ BK-205 │
│ BK-233 │
│ BK-310 │
└────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < json_paths.sql

doc -> '$.city' kept the quotes of the JSON string, "Pune", while doc ->> '$.city' gave the SQL text Pune: mix the two in a comparison or a join and nothing matches. json_each() turned each order’s items into rows, so a plain WHERE found the four lines with a quantity of 2 or more, and GROUP BY produced the five city totals. The last query shows SQL’s version of the missing member: ->> gave NULL both for BK-20002’s "coupon": null and for BK-20006, which has no coupon, and json_type() told them apart: null for BK-20002, and for BK-20006 an empty cell, SQL NULL. Last, json_tree(), which walks every level as .. does, listed the four skus of the shop.

PostgreSQL has arrows too, ->, ->> and #>> for a whole path, and a path language of its own, SQL/JSON path, with filters written ? (@.qty >= 2), last for the last position, and two modes. In lax mode: The default mode of PostgreSQL's SQL/JSON paths: an array is unwrapped when a path asks for a member, and a step that does not fit the data matches nothing. In strict mode the same path raises an error., the default, PostgreSQL bends the data to fit the path: it unwraps an array where the path asks for a member, and a step that still does not fit simply finds nothing. Strict mode turns the same mismatch into an error. This session starts a throwaway PostgreSQL 18.6 server, loads the same orders into a jsonb column and runs nine queries:

SQL/JSON paths and arrows in PostgreSQL 18.6 Python · postgres/paths_session.py
"""SQL/JSON paths and the arrow operators of PostgreSQL 18.6, on the bookshop's orders in a jsonb column."""

from pg_session import Server

with Server() as pg:
    pg.up()
    pg.psql("setup.sql", flags=("-q",))
    pg.psql("paths.sql", errors=1, note="the strict query is meant to fail: its error is the point")

Output

$ psql -X -q -f setup.sql

-- the strict query is meant to fail: its error is the point
$ psql -X -a -f paths.sql
-- Arrows with keys and positions: -> keeps jsonb, ->> gives text, #>> follows a whole path.
SELECT doc->>'id' AS id, doc->'items'->0->>'sku' AS first_sku, doc #>> '{items,0,qty}' AS first_qty
FROM orders WHERE doc->>'city' = 'Pune' ORDER BY 1;
    id    | first_sku | first_qty 
----------+-----------+-----------
 BK-20001 | BK-101    | 2
 BK-20003 | BK-310    | 1
 BK-20008 | BK-101    | 1
(3 rows)

-- The SQL/JSON path language: @? asks whether a path with a filter finds anything.
SELECT doc->>'id' AS id FROM orders WHERE doc @? '$.items[*] ? (@.qty >= 2)' ORDER BY 1;
    id    
----------
 BK-20001
 BK-20004
 BK-20005
 BK-20007
(4 rows)

-- A filter on the whole document: $ ? (…) keeps the document when the condition holds for it.
SELECT doc->>'id' AS id FROM orders WHERE doc @? '$ ? (@.city == "Pune")' ORDER BY 1;
    id    
----------
 BK-20001
 BK-20003
 BK-20008
(3 rows)

-- jsonb_path_query returns one row for every item the path finds, and last is the last position.
SELECT doc->>'id' AS id, jsonb_path_query(doc, '$.items[*] ? (@.qty >= 2).sku') AS sku FROM orders ORDER BY 1;
    id    |   sku    
----------+----------
 BK-20001 | "BK-101"
 BK-20004 | "BK-205"
 BK-20005 | "BK-101"
 BK-20007 | "BK-310"
(4 rows)

SELECT doc->>'id' AS id, jsonb_path_query_first(doc, '$.items[last].sku') AS last_sku FROM orders ORDER BY 1 LIMIT 3;
    id    | last_sku 
----------+----------
 BK-20001 | "BK-205"
 BK-20002 | "BK-233"
 BK-20003 | "BK-310"
(3 rows)

-- Lax mode, the default, unwraps the items array by itself; strict mode refuses the same path.
SELECT jsonb_path_query_array(doc, 'lax $.items.sku') AS lax_skus FROM orders WHERE doc->>'id' = 'BK-20001';
       lax_skus       
----------------------
 ["BK-101", "BK-205"]
(1 row)

SELECT jsonb_path_query_array(doc, 'strict $.items.sku') AS strict_skus FROM orders WHERE doc->>'id' = 'BK-20001';
psql:paths.sql:13: ERROR:  jsonpath member accessor can only be applied to an object
-- .** reaches every level, a PostgreSQL extension that its documentation recommends using in strict mode only.
SELECT DISTINCT jsonb_path_query(doc, 'strict $.**.sku') AS sku FROM orders ORDER BY 1;
   sku    
----------
 "BK-101"
 "BK-205"
 "BK-233"
 "BK-310"
(4 rows)

-- The totals of the paid orders per city, with @@ testing a path predicate.
SELECT doc->>'city' AS city, count(*) AS orders, sum((doc->>'amount_paise')::bigint) AS paise
FROM orders WHERE doc @@ '$.status == "paid"' GROUP BY 1 ORDER BY paise DESC;
   city   | orders | paise  
----------+--------+--------
 Pune     |      2 | 124600
 Mysuru   |      1 | 108300
 Kochi    |      2 |  72400
 Guwahati |      1 |  69800
 Indore   |      1 |  59700
(5 rows)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 paths_session.py

@? asks whether a path finds anything: with a filter on the items it found the same four orders as SQLite’s json_each(), and with $ ? (@.city == "Pune") the three orders from Pune, while jsonb_path_query() returned one row for every sku it found. The two queries on $.items.sku show the modes: lax mode unwrapped the items array by itself and returned both skus, while strict mode refused with jsonpath member accessor can only be applied to an object. In JSONPath, the same $.items.sku would select nothing. .** reaches every level, like JSONPath’s .., and the documentation recommends it in strict mode only; it found the same four skus as SQLite’s json_tree(). The totals per city came out as in SQLite, with @@ testing the predicate $.status == "paid".

Checkpoint

Check: JSON paths in SQLite and PostgreSQL

  1. Question 1 of 2 In SQLite, what do doc -> '$.city' and doc ->> '$.city' give for an order from Pune?

    Choose one answer.

    Show the answer: Question 1 of 2

    Answer: The JSON text "Pune", with its quotes, and the SQL text Pune

    The script printed "Pune" and Pune side by side. Compare and join with ->>, or the quotes make every comparison fail.

  2. Question 2 of 2 What does jsonb_path_query_array(doc, 'lax $.items.sku') give for BK-20001, whose items are an array?

    Choose one answer.

    Show the answer: Question 2 of 2

    Answer: ["BK-101", "BK-205"]

    Lax mode, the default, bends the data to fit the path, here by unwrapping the items array; strict mode turns the same mismatch into an error.

One question, four answers

Here is the manager’s question in each language, the paid orders’ total for each city:

Language How it answered Where the grouping and the sums were done
jq one program of five filters, five totals sorted by city in jq: group_by and add
JMESPath one sum() for each city, written into the expression in JMESPath, but only for cities named in advance
JSONPath a filter that selects the paid orders in the program around it, as below
SQL GROUP BY on the values that ->> read in SQL, in SQLite and PostgreSQL alike

JSONPath selects and stops, so the program around it does the rest:

JSONPath selects the paid orders, Python adds them up Python · python/totals.py
"""The totals of the paid orders per city: JSONPath selects the orders, and Python does the grouping and the sums."""
import json
from collections import defaultdict

from jsonpath_mini import query

with open("orders.json", encoding="utf-8") as f:
    data = json.load(f)

totals = defaultdict(int)
for _, order in query("$.orders[?@.status == 'paid']", data):
    totals[order["city"]] += order["amount_paise"]

for city, paise in sorted(totals.items(), key=lambda kv: -kv[1]):
    print(f"{city:<9} {paise:>7}")
print("JSONPath could select the amounts, $.orders[?@.status == 'paid'].amount_paise, but not add them up")

Output

Pune       124600
Mysuru     108300
Kochi       72400
Guwahati    69800
Indore      59700
JSONPath could select the amounts, $.orders[?@.status == 'paid'].amount_paise, but not add them up

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 totals.py

The four answers agree: 124600 paise for Pune, 108300 for Mysuru, 72400 for Kochi, 69800 for Guwahati and 59700 for Indore, 434800 in all. They differ in what each language had to be told. jq and SQL found the cities in the data, JMESPath had to be given them, and JSONPath needed a program around it. A sixth city in tomorrow’s orders would show up at once in the jq and SQL answers and in the Python loop, while the JMESPath expression would need a new sum() written into it, which is the cost of a language without grouping.

Key idea

JSONPath selects, JMESPath selects and reshapes, jq computes anything, and SQL paths read values for SQL to group: pick the language whose job matches yours, and its query stays short.

Checkpoint

Check: One question in four languages

  1. One question Which language needed the cities written into the query itself to give the total of each city?

    Choose one answer.

    Show the answer: One question

    Answer: JMESPath

    jq and SQL found the five cities themselves; JMESPath had to be given them, and JSONPath needed a program around it.

Translating between them, and the traps

Most simple queries translate line for line. The two tables use the orders of this lesson:

Task JSONPath (RFC 9535) JMESPath
A member $.orders[0].id orders[0].id
The last element $.orders[-1] orders[-1]
One member of every element $.orders[*].id orders[*].id
Every value of a name, at any depth $..sku no counterpart
A filter $.orders[?@.city == 'Pune'] orders[?city == 'Pune']
Task jq SQLite · PostgreSQL
A member .orders[0].id doc ->> '$.id' · doc->>'id'
The last element .items[-1] doc -> '$.items[#-1]' · jsonb_path_query(doc, '$.items[last]')
One member of every element .items[].sku or [.items[].sku] json_each() · '$.items[*].sku'
Every value of a name, at any depth [.. | .sku? | strings] json_tree() · 'strict $.**.sku'
A filter .orders[] | select(.city == "Pune") WHERE doc ->> '$.city' = 'Pune' · WHERE doc @? '$ ? (@.city == "Pune")'

The traps are where queries that look alike mean different things. The program asks three questions in JSONPath and in JMESPath on the same orders, the one about quotes twice, so it prints four pairs of answers:

Three questions in JSONPath and JMESPath that look alike Python · python/traps.py
"""Three questions asked in JSONPath and in JMESPath on the orders, where the same-looking query gives another answer."""
import json

from jmespath_mini import search
from jsonpath_mini import query

with open("orders.json", encoding="utf-8") as f:
    data = json.load(f)

PAIRS = [
    ("$.orders[?@.coupon == null].id", "orders[?coupon == `null`].id"),
    ("$.orders[?@.city == \"Pune\"].id", "orders[?city == \"Pune\"].id"),
    ("$.orders[?@.city == \"Pune\"].id", "orders[?city == 'Pune'].id"),
    ("$.orders[0].items.sku", "orders[0].items.sku"),
]

for jsonpath, jmespath in PAIRS:
    print(f"JSONPath  {jsonpath:<36} {json.dumps([value for _, value in query(jsonpath, data)])}")
    print(f"JMESPath  {jmespath:<36} {json.dumps(search(jmespath, data))}")

Output

JSONPath  $.orders[?@.coupon == null].id       ["BK-20002"]
JMESPath  orders[?coupon == `null`].id         ["BK-20001", "BK-20002", "BK-20003", "BK-20004", "BK-20005", "BK-20006", "BK-20007", "BK-20008"]
JSONPath  $.orders[?@.city == "Pune"].id       ["BK-20001", "BK-20003", "BK-20008"]
JMESPath  orders[?city == "Pune"].id           []
JSONPath  $.orders[?@.city == "Pune"].id       ["BK-20001", "BK-20003", "BK-20008"]
JMESPath  orders[?city == 'Pune'].id           ["BK-20001", "BK-20003", "BK-20008"]
JSONPath  $.orders[0].items.sku                []
JMESPath  orders[0].items.sku                  null

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 traps.py

  • A missing member. $.orders[?@.coupon == null] kept only BK-20002, whose coupon is null, because in JSONPath a missing member is Nothing. JMESPath and jq read a missing member as null, so their versions kept all 8 orders, as the jq session’s select(.coupon == null) count showed. SQLite’s ->> gives NULL for both, while json_type() tells them apart.
  • Quotes. In JMESPath, 'Pune' is a string and "Pune" is the name of a field, so orders[?city == "Pune"] compared each city with a field called Pune and kept nothing. JSONPath takes either quote, jq takes double quotes, SQL takes single quotes for strings, and PostgreSQL’s path strings use double quotes inside the SQL string.
  • A name on an array. $.orders[0].items.sku selected nothing in JSONPath, JMESPath gave null, jq stopped with Cannot index array with string ("sku"), and PostgreSQL’s lax mode returned both skus.
  • Numbers. jq computes in double precision and keeps unchanged literals, Python’s JSON parser keeps integers exact, so the two Python evaluators of this lesson do too, and SQL has its own integer types: sum() in SQLite added the amounts as integers.

The two interpreters behind the Python programs of this lesson are here, the JMESPath one written from the specification and the JSONPath one of the last lesson, so that each result above can be traced to the rule that produced it:

The JMESPath and JSONPath interpreters that the programs import

python/jmespath_mini.py

"""jmespath_mini: a small JMESPath interpreter (jmespath.org/specification.html), standard library only.

    from jmespath_mini import search
    search("orders[?city == 'Pune'].amount_paise | sum(@)", data)

It reads an expression the way jmespath.py does, the library behind the AWS CLI's --query option: a lexer, a parser
that orders operators by binding power, and an interpreter over the parsed tree. Where jmespath.py goes beyond the
specification, this follows jmespath.py: <, <=, > and >= also order two strings. Nothing is passed to eval().
"""

import json
import math

__all__ = ["search", "JMESPathError"]


class JMESPathError(ValueError):
    """A syntax error, a function called with the wrong types or number of arguments, or an unknown function."""


# ---- the lexer -----------------------------------------------------------------------------------------------------

_SIMPLE = {".": "dot", "*": "star", "]": "rbracket", ",": "comma", ":": "colon", "@": "current", "(": "lparen", ")": "rparen", "{": "lbrace", "}": "rbrace"}
_IDENT_START = set("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_")
_IDENT = _IDENT_START | set("0123456789")


def _tokens(text):
    out, i, n = [], 0, len(text)

    def until(delim):
        nonlocal i
        start, buf = i, []
        i += 1
        while i < n and text[i] != delim:
            if text[i] == "\\" and i + 1 < n:
                buf.append(text[i])
                i += 1
            buf.append(text[i])
            i += 1
        if i >= n:
            raise JMESPathError(f"the text that starts at position {start} has no closing {delim}")
        i += 1
        return "".join(buf)

    while i < n:
        c = text[i]
        if c in _SIMPLE:
            out.append((_SIMPLE[c], c))
            i += 1
        elif c in _IDENT_START:
            j = i + 1
            while j < n and text[j] in _IDENT:
                j += 1
            out.append(("name", text[i:j]))
            i = j
        elif c in " \t\n\r":
            i += 1
        elif c == "[":
            nxt = text[i + 1 : i + 2]
            kind = {"]": "flatten", "?": "filter"}.get(nxt, "lbracket")
            out.append((kind, c))
            i += 1 if kind == "lbracket" else 2
        elif c == "'":
            out.append(("literal", until("'").replace("\\'", "'")))  # a raw string: no escapes but \'
        elif c == "`":
            out.append(("literal", _json_literal(until("`").replace("\\`", "`"))))
        elif c == '"':
            try:
                name = json.loads('"' + until('"') + '"')
            except json.JSONDecodeError:
                raise JMESPathError("a quoted name must be a JSON string") from None
            out.append(("quoted", name))  # a quoted name: never a function name
        elif c in "|&":
            double = text[i + 1 : i + 2] == c
            out.append(({"|": "or", "&": "and"}[c] if double else {"|": "pipe", "&": "expref"}[c], c))
            i += 2 if double else 1
        elif c in "<>!=":
            eq = text[i + 1 : i + 2] == "="
            if c == "=" and not eq:
                raise JMESPathError("compare with ==, a single = is not an operator")
            kind = {"<": "lt", ">": "gt", "!": "ne" if eq else "not", "=": "eq"}[c] + ("e" if eq and c in "<>" else "")
            out.append((kind, c))
            i += 2 if eq else 1
        elif c == "-" or c.isdigit() and c.isascii():
            j = i + 1
            while j < n and text[j].isdigit() and text[j].isascii():
                j += 1
            if text[i:j] == "-":
                raise JMESPathError("a minus sign must be followed by digits")
            out.append(("number", int(text[i:j])))
            i = j
        else:
            raise JMESPathError(f"unexpected character {c!r} at position {i}")
    out.append(("eof", None))
    return out


def _json_literal(text):
    try:
        return json.loads(text)
    except json.JSONDecodeError:
        try:
            return json.loads('"' + text.lstrip() + '"')  # old literal syntax for text, which jmespath.py still reads
        except json.JSONDecodeError:
            raise JMESPathError(f"`{text}` is not JSON") from None


# ---- the parser ----------------------------------------------------------------------------------------------------

_POWER = {
    "pipe": 1, "or": 2, "and": 3, "eq": 5, "gt": 5, "lt": 5, "gte": 5, "lte": 5, "ne": 5, "flatten": 9, "star": 20,
    "filter": 21, "dot": 40, "not": 45, "lbrace": 50, "lbracket": 55, "lparen": 60,
}  # every other token binds with 0
_PROJECTION_STOP = 10


class _Parser:
    def __init__(self, text):
        self.tokens, self.i = _tokens(text), 0

    def kind(self, ahead=0):
        return self.tokens[min(self.i + ahead, len(self.tokens) - 1)][0]

    def take(self):
        token = self.tokens[self.i]
        self.i = min(self.i + 1, len(self.tokens) - 1)
        return token

    def expect(self, kind):
        if self.kind() != kind:
            raise JMESPathError(f"expected {kind}, found {self.kind()}")
        self.take()

    def parse(self):
        node = self.expression(0)
        if self.kind() != "eof":
            raise JMESPathError(f"unexpected {self.kind()} after a complete expression")
        return node

    def expression(self, power):
        left = self.nud(self.take())
        while power < _POWER.get(self.kind(), 0):
            left = self.led(self.take(), left)
        return left

    def nud(self, token):
        kind, value = token
        if kind == "literal":
            return ("literal", value)
        if kind in ("name", "quoted"):
            if kind == "quoted" and self.kind() == "lparen":
                raise JMESPathError("a function name cannot be quoted")
            return ("field", value)
        if kind == "star":
            right = ("identity",) if self.kind() == "rbracket" else self.projection_rhs(_POWER["star"])
            return ("value_projection", ("identity",), right)
        if kind == "filter":
            return self.filter_projection(("identity",))
        if kind == "lbrace":
            return self.multi_select_hash()
        if kind == "lparen":
            inner = self.expression(0)
            self.expect("rparen")
            return inner
        if kind == "flatten":
            return ("projection", ("flatten", ("identity",)), self.projection_rhs(_POWER["flatten"]))
        if kind == "not":
            return ("not", self.expression(_POWER["not"]))
        if kind == "lbracket":
            if self.kind() in ("number", "colon"):
                return self.index_or_slice(("identity",))
            if self.kind() == "star" and self.kind(1) == "rbracket":
                self.take(), self.take()
                return ("projection", ("identity",), self.projection_rhs(_POWER["star"]))
            return self.multi_select_list()
        if kind == "current":
            return ("current",)
        if kind == "expref":
            return ("expref", self.expression(0))
        raise JMESPathError(f"unexpected {kind}")

    def led(self, token, left):
        kind = token[0]
        if kind == "dot":
            if self.kind() == "star":
                self.take()
                return ("value_projection", left, self.projection_rhs(_POWER["dot"]))
            return ("subexpression", left, self.dot_rhs(_POWER["dot"]))
        if kind in ("pipe", "or", "and"):
            return (kind, left, self.expression(_POWER[kind]))
        if kind in ("eq", "ne", "lt", "lte", "gt", "gte"):
            return ("compare", kind, left, self.expression(_POWER[kind]))
        if kind == "lparen":
            if left[0] != "field":
                raise JMESPathError("only a function name can be followed by (")
            args = []
            while self.kind() != "rparen":
                args.append(self.expression(0))
                if self.kind() == "comma":
                    self.take()
            self.take()
            return ("function", left[1], args)
        if kind == "filter":
            return self.filter_projection(left)
        if kind == "flatten":
            return ("projection", ("flatten", left), self.projection_rhs(_POWER["flatten"]))
        if kind == "lbracket":
            if self.kind() in ("number", "colon"):
                return self.index_or_slice(left)
            self.expect("star")
            self.expect("rbracket")
            return ("projection", left, self.projection_rhs(_POWER["star"]))
        raise JMESPathError(f"unexpected {kind}")

    def index_or_slice(self, left):
        if self.kind() == "colon" or self.kind(1) == "colon":
            parts, at = [None, None, None], 0
            while self.kind() != "rbracket":
                if self.kind() == "colon":
                    at += 1
                    if at == 3:
                        raise JMESPathError("a slice has at most three parts: [start:stop:step]")
                    self.take()
                elif self.kind() == "number":
                    parts[at] = self.take()[1]
                else:
                    raise JMESPathError("a slice holds only whole numbers")
            self.take()
            return ("projection", ("slice", left, *parts), self.projection_rhs(_POWER["star"]))
        index = self.take()[1]
        self.expect("rbracket")
        return ("index", left, index)

    def filter_projection(self, left):
        condition = self.expression(0)
        self.expect("rbracket")
        right = ("identity",) if self.kind() == "flatten" else self.projection_rhs(_POWER["filter"])
        return ("filter_projection", left, condition, right)

    def projection_rhs(self, power):
        """What a projection applies to each item: nothing, or a chain of .name, [index] and [?filter] steps."""
        if _POWER.get(self.kind(), 0) < _PROJECTION_STOP:
            return ("identity",)
        if self.kind() in ("lbracket", "filter"):
            return self.expression(power)
        if self.kind() == "dot":
            self.take()
            return self.dot_rhs(power)
        raise JMESPathError(f"unexpected {self.kind()} after a projection")

    def dot_rhs(self, power):
        if self.kind() in ("name", "quoted", "star"):
            return self.expression(power)
        if self.kind() == "lbracket":
            self.take()
            return self.multi_select_list()
        if self.kind() == "lbrace":
            self.take()
            return self.multi_select_hash()
        raise JMESPathError("after a dot comes a name, *, [ … ] or { … }")

    def multi_select_list(self):
        items = []
        while True:
            items.append(self.expression(0))
            if self.kind() == "rbracket":
                self.take()
                return ("list", items)
            self.expect("comma")

    def multi_select_hash(self):
        pairs = []
        while True:
            kind, key = self.take()
            if kind not in ("name", "quoted"):
                raise JMESPathError("a multi-select hash holds key: expression pairs")
            self.expect("colon")
            pairs.append((key, self.expression(0)))
            if self.kind() == "rbrace":
                self.take()
                return ("hash", pairs)
            self.expect("comma")


# ---- values --------------------------------------------------------------------------------------------------------


class _ExprRef:
    """&expression, for sort_by(), max_by(), min_by() and map()."""

    def __init__(self, node):
        self.node = node


def _type(v):
    if v is None:
        return "null"
    if isinstance(v, bool):
        return "boolean"
    if isinstance(v, (int, float)):
        return "number"
    if isinstance(v, str):
        return "string"
    if isinstance(v, list):
        return "array"
    if isinstance(v, _ExprRef):
        return "expref"
    return "object"


def _false(v):
    """JMESPath's false values: null, false, "", [] and {} (0 is true)."""
    return v is None or v is False or (isinstance(v, (str, list, dict)) and not v)


def _equal(a, b):
    if _type(a) != _type(b):
        return False
    if isinstance(a, list):
        return len(a) == len(b) and all(_equal(x, y) for x, y in zip(a, b))
    if isinstance(a, dict):
        return a.keys() == b.keys() and all(_equal(a[k], b[k]) for k in a)
    return a == b


# ---- the interpreter -----------------------------------------------------------------------------------------------


def _visit(node, value):
    kind = node[0]
    if kind == "field":
        return value.get(node[1]) if isinstance(value, dict) else None
    if kind == "subexpression":
        return _visit(node[2], _visit(node[1], value))
    if kind == "pipe":
        return _visit(node[2], _visit(node[1], value))
    if kind == "index":
        base = _visit(node[1], value)
        if not isinstance(base, list):
            return None
        i = node[2] + len(base) if node[2] < 0 else node[2]
        return base[i] if 0 <= i < len(base) else None
    if kind == "slice":
        base = _visit(node[1], value)
        if not isinstance(base, list):
            return None
        if node[4] == 0:
            raise JMESPathError("a slice step cannot be 0")
        return base[node[2] : node[3] : node[4]]
    if kind in ("projection", "value_projection", "filter_projection"):
        base = _visit(node[1], value)
        if kind == "value_projection":
            items = list(base.values()) if isinstance(base, dict) else None
        else:
            items = base if isinstance(base, list) else None
        if items is None:
            return None
        if kind == "filter_projection":
            items = [item for item in items if not _false(_visit(node[2], item))]
        right = node[3] if kind == "filter_projection" else node[2]
        results = [_visit(right, item) for item in items]
        return [r for r in results if r is not None]  # a projection leaves out the items whose result is null
    if kind == "flatten":
        base = _visit(node[1], value)
        if not isinstance(base, list):
            return None
        out = []
        for item in base:
            out.extend(item) if isinstance(item, list) else out.append(item)
        return out
    if kind in ("identity", "current"):
        return value
    if kind == "literal":
        return node[1]
    if kind == "list":
        return None if value is None else [_visit(child, value) for child in node[1]]
    if kind == "hash":
        return None if value is None else {key: _visit(child, value) for key, child in node[1]}
    if kind == "or":
        left = _visit(node[1], value)
        return _visit(node[2], value) if _false(left) else left
    if kind == "and":
        left = _visit(node[1], value)
        return left if _false(left) else _visit(node[2], value)
    if kind == "not":
        return _false(_visit(node[1], value))
    if kind == "compare":
        op, left, right = node[1], _visit(node[2], value), _visit(node[3], value)
        if op == "eq":
            return _equal(left, right)
        if op == "ne":
            return not _equal(left, right)
        if not ((_type(left) == _type(right) == "number") or (_type(left) == _type(right) == "string")):
            return None  # ordering compares two numbers, or two strings as jmespath.py allows; anything else is null
        return {"lt": left < right, "lte": left <= right, "gt": left > right, "gte": left >= right}[op]
    if kind == "expref":
        return _ExprRef(node[1])
    if kind == "function":
        return _call(node[1], [_visit(arg, value) for arg in node[2]])
    raise JMESPathError(f"cannot evaluate {kind}")


# ---- the built-in functions (specification section "Built-in Functions") -------------------------------------------

_SIGNATURES = {
    "abs": [["number"]], "avg": [["array-number"]], "ceil": [["number"]], "contains": [["array", "string"], []],
    "ends_with": [["string"], ["string"]], "floor": [["number"]], "join": [["string"], ["array-string"]], "keys": [["object"]],
    "length": [["string", "array", "object"]], "map": [["expref"], ["array"]], "max": [["array-number", "array-string"]],
    "max_by": [["array"], ["expref"]], "merge": [["object"], "..."], "min": [["array-number", "array-string"]],
    "min_by": [["array"], ["expref"]], "not_null": [[], "..."], "reverse": [["array", "string"]],
    "sort": [["array-string", "array-number"]], "sort_by": [["array"], ["expref"]], "starts_with": [["string"], ["string"]],
    "sum": [["array-number"]], "to_array": [[]], "to_number": [[]], "to_string": [[]], "type": [[]], "values": [["object"]],
}


def _check(name, args):
    if name not in _SIGNATURES:
        raise JMESPathError(f"unknown function {name}()")
    params = _SIGNATURES[name]
    variadic = params[-1] == "..."
    fixed = params[:-1] if variadic else params
    if (len(args) < len(fixed)) if variadic else (len(args) != len(fixed)):
        raise JMESPathError(f"{name}() takes {'at least ' if variadic else ''}{len(fixed)} argument(s), {len(args)} given")
    for i, arg in enumerate(args):
        allowed = fixed[min(i, len(fixed) - 1)]
        if not allowed:
            continue
        t = _type(arg)
        if t not in [a.split("-")[0] for a in allowed]:
            raise JMESPathError(f"{name}() does not take {t} as argument {i + 1}")
        item_types = [a.split("-")[1] for a in allowed if a.startswith(t + "-")]
        if t == "array" and item_types and "array" not in allowed and arg:
            want = item_types[0] if len(item_types) == 1 else _type(arg[0])
            if want not in item_types or any(_type(x) != want for x in arg):
                raise JMESPathError(f"{name}() needs an array of {' or '.join(item_types)}s as argument {i + 1}")


def _key(ref, item, allowed, name):
    k = _visit(ref.node, item)
    if _type(k) not in allowed:
        raise JMESPathError(f"{name}() needs the expression to give a {' or '.join(allowed)} for every item")
    return k


def _call(name, args):
    _check(name, args)
    a = args[0] if args else None
    if name == "abs":
        return abs(a)
    if name == "avg":
        return sum(a) / len(a) if a else None
    if name == "ceil":
        return math.ceil(a)
    if name == "floor":
        return math.floor(a)
    if name == "contains":
        if isinstance(a, str):
            return isinstance(args[1], str) and args[1] in a
        return any(_equal(x, args[1]) for x in a)
    if name == "ends_with":
        return a.endswith(args[1])
    if name == "starts_with":
        return a.startswith(args[1])
    if name == "join":
        return a.join(args[1])
    if name == "keys":
        return list(a.keys())
    if name == "values":
        return list(a.values())
    if name == "length":
        return len(a)
    if name == "map":
        return [_visit(a.node, item) for item in args[1]]
    if name in ("max", "min"):
        return (max if name == "max" else min)(a) if a else None
    if name in ("max_by", "min_by"):
        if not a:
            return None
        keys = [_key(args[1], item, ("number", "string"), name) for item in a]
        if len({_type(k) for k in keys}) > 1:
            raise JMESPathError(f"{name}() needs the expression to give only numbers or only strings")
        best = (max if name == "max_by" else min)(range(len(a)), key=lambda i: keys[i])
        return a[best]
    if name == "merge":
        out = {}
        for obj in args:
            out.update(obj)
        return out
    if name == "not_null":
        return next((x for x in args if x is not None), None)
    if name == "reverse":
        return a[::-1]
    if name == "sort":
        return sorted(a)
    if name == "sort_by":
        if not a:
            return a
        first = _type(_visit(args[1].node, a[0]))
        if first not in ("number", "string"):
            raise JMESPathError("sort_by() needs the expression to give numbers or strings")
        return sorted(a, key=lambda item: _key(args[1], item, (first,), name))
    if name == "sum":
        return sum(a)
    if name == "to_array":
        return a if isinstance(a, list) else [a]
    if name == "to_number":
        if _type(a) == "number":
            return a
        if isinstance(a, str):
            for convert in (int, float):
                try:
                    return convert(a)
                except ValueError:
                    pass
        return None
    if name == "to_string":
        return a if isinstance(a, str) else json.dumps(a, separators=(",", ":"))
    if name == "type":
        return _type(a)
    raise JMESPathError(f"unknown function {name}()")


def search(expression, data):
    """The value that a JMESPath expression gives for the data (None for null)."""
    result = _visit(_Parser(expression).parse(), data)
    if isinstance(result, _ExprRef):
        raise JMESPathError("an expression reference (&…) has no JSON value of its own")
    return result

python/jsonpath_mini.py

"""jsonpath_mini: a small JSONPath evaluator that follows RFC 9535, written with the standard library only.

    from jsonpath_mini import query
    for path, value in query("$.books[*].title", catalogue):
        print(path, value)

query() reads the whole query before it looks at the document, so a query that RFC 9535 does not allow raises
JSONPathSyntaxError, with the position of the problem, before anything is selected. Nothing in a query is ever run as
code. The result is the nodelist, a list of (normalized path, value) pairs in the order RFC 9535 gives, with object
members in the order of the document. Section numbers in the comments are those of RFC 9535.
"""

import functools
import re
import unicodedata

MAX_EXACT = 2**53 - 1  # integers in a query stay in the I-JSON range of exact values (section 2.1)
BLANK = " \t\n\r"  # the blank space allowed between the parts of a query (section 2.1.1)
DIGITS = "0123456789"


class JSONPathSyntaxError(ValueError):
    """The text is not a JSONPath query of RFC 9535; .position is where the problem was found."""

    def __init__(self, message, position):
        super().__init__(f"{message} (at position {position})")
        self.position = position


class _Nothing:
    """The special result Nothing (section 2.4.1): the absence of a value, which is not null."""

    def __repr__(self):
        return "Nothing"


NOTHING = _Nothing()


def query(text, document):
    """The nodelist that the query selects from the document: a list of (normalized path, value)."""
    segments = _Parser(text).parse()
    return [(normalized_path(path), value) for path, value in _apply(segments, [((), document)], document)]


# ---- running segments and selectors --------------------------------------------------------------------------------
# A node is (path, value): the path is a tuple of member names and array positions from the root.


def _apply(segments, nodes, root):
    """Applies each segment to every node of the nodelist so far, in order (section 2.1.2)."""
    for descendant, selectors in segments:
        out = []
        for node in nodes:
            for target in _self_and_descendants(node) if descendant else (node,):
                for selector in selectors:
                    out.extend(selector(target, root))
        nodes = out
    return nodes


def _children(node):
    """The member values of an object, or the elements of an array, in order (sections 1.1, 2.3.2)."""
    path, value = node
    if isinstance(value, dict):
        return [(path + (name,), item) for name, item in value.items()]
    if isinstance(value, list):
        return [(path + (i,), item) for i, item in enumerate(value)]
    return []


def _self_and_descendants(node):
    """The node, then its descendants: every node before its descendants, arrays in order (section 2.5.2).
    A list of nodes still to visit, not recursion, so a deeply nested document cannot overflow the stack."""
    stack = [node]
    while stack:
        current = stack.pop()
        yield current
        stack.extend(reversed(_children(current)))


def _name_selector(name):
    def select(node, root):
        path, value = node
        if isinstance(value, dict) and name in value:
            return [(path + (name,), value[name])]
        return []  # nothing is selected from a value that is not an object (section 2.3.1.2)

    return select


def _wildcard_selector(node, root):
    return _children(node)


def _index_selector(index):
    def select(node, root):
        path, value = node
        if isinstance(value, list):
            i = index + len(value) if index < 0 else index  # -1 is the last element (section 2.3.3.2)
            if 0 <= i < len(value):
                return [(path + (i,), value[i])]
        return []

    return select


def slice_positions(length, start, end, step):
    """The positions that start:end:step selects in an array of this length (section 2.3.4.2.2)."""
    step = 1 if step is None else step
    if step == 0:
        return []
    if start is None:
        start = 0 if step > 0 else length - 1
    if end is None:
        end = length if step > 0 else -length - 1

    def normalize(i):
        return i if i >= 0 else length + i

    if step > 0:
        lower = min(max(normalize(start), 0), length)
        upper = min(max(normalize(end), 0), length)
        return list(range(lower, upper, step))
    upper = min(max(normalize(start), -1), length - 1)
    lower = min(max(normalize(end), -1), length - 1)
    return list(range(upper, lower, step))


def _slice_selector(start, end, step):
    def select(node, root):
        path, value = node
        if not isinstance(value, list):
            return []
        return [(path + (i,), value[i]) for i in slice_positions(len(value), start, end, step)]

    return select


def _filter_selector(expression):
    def select(node, root):
        return [child for child in _children(node) if expression.test(child[1], root)]

    return select


# ---- normalized paths (section 2.7) --------------------------------------------------------------------------------

_PATH_ESCAPES = {"\b": "\\b", "\f": "\\f", "\n": "\\n", "\r": "\\r", "\t": "\\t", "'": "\\'", "\\": "\\\\"}


def normalized_path(path):
    """$['books'][0]['title']: single quotes, the escapes of section 2.7, non-negative positions."""
    parts = ["$"]
    for step in path:
        if isinstance(step, int):
            parts.append(f"[{step}]")
        else:
            name = "".join(_PATH_ESCAPES.get(c) or (f"\\u{ord(c):04x}" if ord(c) < 0x20 else c) for c in step)
            parts.append(f"['{name}']")
    return "".join(parts)


# ---- filter expressions (sections 2.3.5 and 2.4) -------------------------------------------------------------------


def _is_number(x):
    return isinstance(x, (int, float)) and not isinstance(x, bool)


def _equal(a, b):
    """== of section 2.3.5.2.2: Nothing equals only Nothing, and values of different types are never equal."""
    if a is NOTHING or b is NOTHING:
        return a is b
    if _is_number(a) and _is_number(b):
        return a == b
    if type(a) is not type(b):
        return False
    if isinstance(a, list):
        return len(a) == len(b) and all(_equal(x, y) for x, y in zip(a, b))
    if isinstance(a, dict):
        return a.keys() == b.keys() and all(_equal(a[k], b[k]) for k in a)
    return a == b


def _less(a, b):
    """< of section 2.3.5.2.2: only two numbers or two strings (by Unicode scalar values) can be ordered."""
    if _is_number(a) and _is_number(b):
        return a < b
    if isinstance(a, str) and isinstance(b, str):
        return a < b
    return False


_COMPARE = {
    "==": _equal,
    "!=": lambda a, b: not _equal(a, b),
    "<": _less,
    "<=": lambda a, b: _less(a, b) or _equal(a, b),
    ">": lambda a, b: _less(b, a),
    ">=": lambda a, b: _less(b, a) or _equal(a, b),
}


class _Literal:
    kind = "value"

    def __init__(self, value):
        self.value = value

    def value_of(self, current, root):
        return self.value


class _Query:
    """@... or $... inside a filter; singular when it can select at most one node (section 2.3.5.1)."""

    kind = "nodes"

    def __init__(self, relative, segments, singular):
        self.relative, self.segments, self.singular = relative, segments, singular

    def nodes(self, current, root):
        return _apply(self.segments, [((), current if self.relative else root)], root)

    def value_of(self, current, root):
        found = self.nodes(current, root)
        return found[0][1] if found else NOTHING

    def test(self, current, root):
        return bool(self.nodes(current, root))  # an existence test (section 2.3.5.2.1)


def _length(v):
    if isinstance(v, (str, list, dict)):
        return len(v)  # a Python str has one item for each Unicode scalar value
    return NOTHING


def _regex_test(how):
    def run(text, pattern):
        if not isinstance(text, str) or not isinstance(pattern, str):
            return False
        compiled = _iregexp(pattern)
        return compiled is not None and (compiled.fullmatch(text) if how == "match" else compiled.search(text)) is not None

    return run


def _value(nodes):
    return nodes[0][1] if len(nodes) == 1 else NOTHING


# name: (the declared types of the parameters, the declared result type, the function) (sections 2.4.4 to 2.4.8)
FUNCTIONS = {
    "length": (("value",), "value", _length),
    "count": (("nodes",), "value", len),
    "match": (("value", "value"), "logical", _regex_test("match")),
    "search": (("value", "value"), "logical", _regex_test("search")),
    "value": (("nodes",), "value", _value),
}


class _Call:
    def __init__(self, name, args):
        self.name, self.args = name, args
        self.params, self.kind, self.function = FUNCTIONS[name]

    def _run(self, current, root):
        values = []
        for param, arg in zip(self.params, self.args):
            values.append(arg.nodes(current, root) if param == "nodes" else arg.value_of(current, root))
        return self.function(*values)

    def value_of(self, current, root):
        return self._run(current, root)

    def test(self, current, root):
        result = self._run(current, root)
        return bool(result)  # LogicalType, or a NodesType result converted (section 2.4.2)


class _Compare:
    def __init__(self, left, op, right):
        self.left, self.op, self.right = left, op, right

    def test(self, current, root):
        return _COMPARE[self.op](self.left.value_of(current, root), self.right.value_of(current, root))


class _Not:
    def __init__(self, inner):
        self.inner = inner

    def test(self, current, root):
        return not self.inner.test(current, root)


class _And:
    def __init__(self, left, right):
        self.left, self.right = left, right

    def test(self, current, root):
        return self.left.test(current, root) and self.right.test(current, root)


class _Or:
    def __init__(self, left, right):
        self.left, self.right = left, right

    def test(self, current, root):
        return self.left.test(current, root) or self.right.test(current, root)


# ---- I-Regexp (RFC 9485), translated to a Python regular expression -------------------------------------------------

_NOT_NORMAL = set("()*+.?[\\]{|}")  # the characters NormalChar leaves out
_SINGLE_ESCAPES = set("()*+-.?[\\]^nrt{|}")  # what may follow a backslash (SingleCharEsc)
_CATEGORIES = {"L": "lmotu", "M": "cen", "N": "dlo", "P": "cdefios", "Z": "lps", "S": "ckmo", "C": "cfno"}


@functools.lru_cache(maxsize=None)
def _category_ranges(prop, complement):
    """The code points of a Unicode general category, as ranges for a character class (\\p{..}, \\P{..})."""
    ranges, start = [], None
    for cp in range(0x110000 + 1):
        inside = cp <= 0x10FFFF and not 0xD800 <= cp <= 0xDFFF
        if inside:
            inside = unicodedata.category(chr(cp)).startswith(prop) != complement
        if inside and start is None:
            start = cp
        elif not inside and start is not None:
            ranges.append(f"\\U{start:08x}-\\U{cp - 1:08x}" if cp - 1 > start else f"\\U{start:08x}")
            start = None
    return "".join(ranges)


class _IRegexp:
    """A recursive-descent reader of the I-Regexp grammar (RFC 9485 section 3) that writes Python's syntax."""

    def __init__(self, text):
        self.text, self.pos = text, 0

    def peek(self):
        return self.text[self.pos] if self.pos < len(self.text) else ""

    def take(self):
        c = self.peek()
        if not c:
            raise ValueError("unexpected end")
        self.pos += 1
        return c

    def regexp(self):
        branches = [self.branch()]
        while self.peek() == "|":
            self.pos += 1
            branches.append(self.branch())
        return "|".join(branches)

    def branch(self):
        out = []
        while self.peek() and self.peek() not in "|)":
            atom = self.atom()
            out.append(atom + self.quantifier())
        return "".join(out)

    def quantifier(self):
        c = self.peek()
        if c in ("*", "+", "?") and c:
            self.pos += 1
            return c
        if c != "{":
            return ""
        m = re.compile(r"\{([0-9]+)(,([0-9]*))?\}").match(self.text, self.pos)
        if not m or (m.group(3) and int(m.group(3)) < int(m.group(1))):
            raise ValueError("bad quantifier")
        self.pos = m.end()
        return m.group(0)

    def atom(self):
        c = self.take()
        if c == "(":
            inner = self.regexp()
            if self.take() != ")":
                raise ValueError("missing )")
            return f"(?:{inner})"
        if c == ".":
            return "[^\\n\\r]"  # RFC 9485 section 5.3: the dot matches anything but a line feed or a return
        if c == "[":
            return self.char_class()
        if c == "\\":
            return self.escape(in_class=False)
        if c in _NOT_NORMAL:
            raise ValueError(f"{c} is not allowed here")
        if c in "^$":
            return c  # NormalChar in RFC 9485's grammar; kept as anchors, as section 5.3's translation does
        return re.escape(c)

    def escape(self, in_class):
        c = self.take()
        if c in ("p", "P"):
            if self.take() != "{":
                raise ValueError("expected {")
            end = self.text.find("}", self.pos)
            prop = self.text[self.pos : end] if end >= 0 else ""
            if not (len(prop) in (1, 2) and prop[0] in _CATEGORIES and (len(prop) == 1 or prop[1] in _CATEGORIES[prop[0]])):
                raise ValueError("unknown category")
            self.pos = end + 1
            ranges = _category_ranges(prop, c == "P")
            return ranges if in_class else f"[{ranges}]"
        if c not in _SINGLE_ESCAPES:
            raise ValueError(f"\\{c} is not an escape of I-Regexp")
        return {"n": "\\n", "r": "\\r", "t": "\\t"}.get(c) or re.escape(c)

    def class_char(self):
        """One CCchar: a character, or a single-character escape; returns (its text for Python, the character)."""
        c = self.take()
        if c == "\\":
            nxt = self.peek()
            if nxt in ("p", "P"):
                return self.escape(in_class=True), None
            text = self.escape(in_class=True)
            return text, {"n": "\n", "r": "\r", "t": "\t"}.get(nxt, nxt)
        if c in "-[]":
            raise ValueError(f"{c} must be escaped in a class")
        return re.escape(c), c

    def char_class(self):
        negated = self.peek() == "^"
        if negated:
            self.pos += 1
        items = []
        if self.peek() == "-":
            self.pos += 1
            items.append("\\-")
        while self.peek() and self.peek() != "]":
            if self.peek() == "-" and self.text[self.pos + 1 : self.pos + 2] == "]":
                self.pos += 1
                items.append("\\-")
                break
            text, char = self.class_char()
            if self.peek() == "-" and self.text[self.pos + 1 : self.pos + 2] not in ("]", ""):
                if char is None:
                    raise ValueError("a category cannot start a range")
                self.pos += 1
                end_text, end_char = self.class_char()
                if end_char is None or ord(end_char) < ord(char):
                    raise ValueError("bad range")
                items.append(f"{text}-{end_text}")
            else:
                items.append(text)
        if self.take() != "]" or not items:
            raise ValueError("bad class")
        return f"[{'^' if negated else ''}{''.join(items)}]"


@functools.lru_cache(maxsize=256)
def _iregexp(pattern):
    """The compiled Python form of an I-Regexp, or None when the pattern is not one (match() is then false)."""
    try:
        reader = _IRegexp(pattern)
        translated = reader.regexp()
        if reader.pos != len(pattern):
            return None
        return re.compile(translated)
    except (ValueError, re.error):
        return None


# ---- the parser (the ABNF of sections 2.1 to 2.5) -----------------------------------------------------------------


class _Parser:
    def __init__(self, text):
        self.text, self.pos = text, 0

    def fail(self, message, at=None):
        raise JSONPathSyntaxError(message, self.pos if at is None else at)

    def peek(self, s):
        return self.text.startswith(s, self.pos)

    def eat(self, s):
        if self.text.startswith(s, self.pos):
            self.pos += len(s)
            return True
        return False

    def blank(self):
        while self.pos < len(self.text) and self.text[self.pos] in BLANK:
            self.pos += 1

    def parse(self):
        if not self.eat("$"):
            self.fail("a query starts with $")
        segments, _ = self.segments()
        if self.pos != len(self.text):
            self.fail("unexpected text")
        return segments

    # -- segments ---------------------------------------------------------------------------------------------------

    def segments(self):
        """*(S segment), and whether the segments keep the query singular (section 2.3.5.1)."""
        segments, singular = [], True
        while True:
            before = self.pos
            self.blank()
            if self.eat(".."):  # a descendant segment: ..[…], ..* or ..name (section 2.5.2)
                selectors = self.bracketed()[0] if self.peek("[") else [self.dotted_name_or_wildcard()[0]]
                segments.append((True, selectors))
                singular = False
            elif self.eat("."):  # .* or .name, shorthand for [*] and ['name'] (section 2.5.1)
                selector, is_name = self.dotted_name_or_wildcard()
                segments.append((False, [selector]))
                singular = singular and is_name
            elif self.peek("["):
                selectors, one_simple = self.bracketed()
                segments.append((False, selectors))
                singular = singular and one_simple
            else:
                self.pos = before  # blank space with no segment after it belongs to what follows
                return segments, singular

    def dotted_name_or_wildcard(self):
        if self.eat("*"):
            return _wildcard_selector, False
        start = self.pos
        if self.pos < len(self.text) and _name_first(self.text[self.pos]):
            self.pos += 1
            while self.pos < len(self.text) and (_name_first(self.text[self.pos]) or self.text[self.pos] in DIGITS):
                self.pos += 1
            return _name_selector(self.text[start : self.pos]), True
        self.fail("expected a member name or * after the dot")

    def bracketed(self):
        """[ selector, selector … ]: the selectors, and whether it is one name or one index without blank space."""
        self.pos += 1  # [
        selectors, simple = [], False
        tight = self.pos < len(self.text) and self.text[self.pos] not in BLANK
        while True:
            self.blank()
            selector, simple = self.selector()
            selectors.append(selector)
            end_tight = self.pos < len(self.text) and self.text[self.pos] in "],"
            self.blank()
            if self.eat("]"):
                break
            if not self.eat(","):
                self.fail("expected , or ]")
        return selectors, len(selectors) == 1 and simple and tight and end_tight

    def selector(self):
        """One selector, and whether it is a name or an index (the selectors a singular query may use)."""
        c = self.text[self.pos : self.pos + 1]
        if c in ("'", '"'):
            return _name_selector(self.string()), True
        if c == "*":
            self.pos += 1
            return _wildcard_selector, False
        if c == "?":
            self.pos += 1
            self.blank()
            return _filter_selector(self.logical_or()), False
        if c == "(":
            self.fail("(…) is a script expression of the 2007 article, which RFC 9535 does not have")
        start = self.integer()
        after = self.pos
        self.blank()
        if not self.peek(":"):
            self.pos = after
            if start is None:
                self.fail("expected a selector")
            return _index_selector(start), True
        self.pos += 1
        self.blank()
        end = self.integer()
        if end is not None:
            after = self.pos
            self.blank()
            if not self.peek(":"):
                self.pos = after
        step = None
        if self.eat(":"):
            after = self.pos
            self.blank()
            step = self.integer()
            if step is None:
                self.pos = after
        return _slice_selector(start, end, step), False

    def integer(self):
        """An integer of the query (section 2.3.3.1): no leading zeros, no -0, inside the I-JSON range; or None."""
        m = re.compile(r"-?[0-9]+").match(self.text, self.pos)
        if not m:
            return None
        digits = m.group().lstrip("-")
        if (digits.startswith("0") and len(digits) > 1) or m.group() == "-0":
            self.fail("integers have no leading zeros, and -0 is not one")
        value = int(m.group())
        if abs(value) > MAX_EXACT:
            self.fail("the integer is outside the range -(2^53 - 1) to 2^53 - 1")
        self.pos = m.end()
        return value

    def string(self):
        """A string literal in single or double quotes, with the escapes of section 2.3.1.1."""
        quote = self.text[self.pos]
        self.pos += 1
        out = []
        while True:
            if self.pos >= len(self.text):
                self.fail("the string has no closing quote")
            c = self.text[self.pos]
            self.pos += 1
            if c == quote:
                return "".join(out)
            if c == "\\":
                out.append(self.string_escape(quote))
            elif ord(c) < 0x20:
                self.fail("a control character must be escaped", self.pos - 1)
            else:
                out.append(c)

    def string_escape(self, quote):
        c = self.text[self.pos : self.pos + 1]
        self.pos += 1
        simple = {"b": "\b", "f": "\f", "n": "\n", "r": "\r", "t": "\t", "/": "/", "\\": "\\", quote: quote}
        if c in simple:
            return simple[c]
        if c != "u":
            self.fail(f"\\{c} is not an escape in a {'single' if quote == chr(39) else 'double'}-quoted string", self.pos - 2)
        code = self.hex4()
        if 0xDC00 <= code <= 0xDFFF:
            self.fail("a low surrogate needs a high surrogate before it")
        if 0xD800 <= code <= 0xDBFF:
            if not self.eat("\\u"):
                self.fail("a high surrogate needs a low surrogate after it")
            low = self.hex4()
            if not 0xDC00 <= low <= 0xDFFF:
                self.fail("a high surrogate needs a low surrogate after it")
            return chr(0x10000 + ((code - 0xD800) << 10) + (low - 0xDC00))
        return chr(code)

    def hex4(self):
        digits = self.text[self.pos : self.pos + 4]
        if len(digits) != 4 or any(d not in "0123456789abcdefABCDEF" for d in digits):
            self.fail("\\u needs four hexadecimal digits")
        self.pos += 4
        return int(digits, 16)

    # -- logical expressions ------------------------------------------------------------------------------------------

    def logical_or(self):
        left = self.logical_and()
        while True:
            before = self.pos
            self.blank()
            if not self.eat("||"):
                self.pos = before
                return left
            self.blank()
            left = _Or(left, self.logical_and())

    def logical_and(self):
        left = self.basic()
        while True:
            before = self.pos
            self.blank()
            if not self.eat("&&"):
                self.pos = before
                return left
            self.blank()
            left = _And(left, self.basic())

    def paren(self):
        self.pos += 1  # (
        self.blank()
        inner = self.logical_or()
        self.blank()
        if not self.eat(")"):
            self.fail("expected )")
        return inner

    def basic(self):
        if self.eat("!"):
            self.blank()
            if self.peek("("):
                return _Not(self.paren())
            at = self.pos
            operand = self.operand()
            self.no_comparison_after("! applies to a test or to a parenthesised expression; write !(a == b)")
            return _Not(self.as_test(operand, at))
        if self.peek("("):
            return self.paren()
        at = self.pos
        left = self.operand()
        before = self.pos
        self.blank()
        op = self.comparison_op()
        if op is None:
            self.pos = before
            return self.as_test(left, at)
        self.check_comparable(left, at)
        self.blank()
        right_at = self.pos
        right = self.operand()
        self.check_comparable(right, right_at)
        self.no_comparison_after("a comparison has one operator; join comparisons with && or ||")
        return _Compare(left, op, right)

    def comparison_op(self):
        for op in ("==", "!=", "<=", ">=", "<", ">"):
            if self.eat(op):
                return op
        return None

    def no_comparison_after(self, message):
        before = self.pos
        self.blank()
        if self.comparison_op() is not None:
            self.fail(message)
        self.pos = before

    def operand(self):
        """A literal, a query that starts with @ or $, or a function expression."""
        c = self.text[self.pos : self.pos + 1]
        if c in ("@", "$"):
            self.pos += 1
            segments, singular = self.segments()
            return _Query(c == "@", segments, singular)
        if c in ("'", '"'):
            return _Literal(self.string())
        if c and c in "-" + DIGITS:
            return _Literal(self.number())
        m = re.compile(r"[a-z][a-z0-9_]*").match(self.text, self.pos)
        if not m:
            self.fail("expected a query, a literal or a function")
        name = m.group()
        if name in ("true", "false", "null") and not self.text.startswith("(", m.end()):
            self.pos = m.end()
            return _Literal({"true": True, "false": False, "null": None}[name])
        if not self.text.startswith("(", m.end()):
            self.fail(f"{name} is not a literal; a function name is followed by (")
        if name not in FUNCTIONS:
            self.fail(f"{name}() is not one of the functions length, count, match, search and value")
        at = self.pos
        self.pos = m.end() + 1
        self.blank()
        args = []
        if not self.peek(")"):
            while True:
                args.append(self.argument())
                self.blank()
                if self.eat(")"):
                    break
                if not self.eat(","):
                    self.fail("expected , or )")
                self.blank()
        else:
            self.pos += 1
        return self.check_call(name, args, at)

    def argument(self):
        """A function argument: a literal, a query or a function alone, else a logical expression."""
        at = self.pos
        if not self.peek("(") and not self.peek("!"):
            operand = self.operand()
            after = self.pos
            self.blank()
            if self.peek(",") or self.peek(")"):
                self.pos = after
                return (operand, at)
            self.pos = at
        return (self.logical_or(), at)

    def check_call(self, name, args, at):
        """Well-typedness (section 2.4.3): every argument fits the declared type of its parameter."""
        params = FUNCTIONS[name][0]
        if len(args) != len(params):
            self.fail(f"{name}() takes {len(params)} argument{'s' if len(params) > 1 else ''}", at)
        checked = []
        for param, (arg, arg_at) in zip(params, args):
            if param == "value":
                ok = isinstance(arg, _Literal) or (isinstance(arg, _Query) and arg.singular) or (isinstance(arg, _Call) and arg.kind == "value")
                why = "a literal, a singular query or a function that gives a value"
            else:
                ok = isinstance(arg, _Query) or (isinstance(arg, _Call) and arg.kind == "nodes")
                why = "a query"
            if not ok:
                self.fail(f"the argument of {name}() must be {why}", arg_at)
            checked.append(arg)
        return _Call(name, checked)

    def check_comparable(self, operand, at):
        if isinstance(operand, _Literal) or (isinstance(operand, _Call) and operand.kind == "value"):
            return
        if isinstance(operand, _Query) and operand.singular:
            return
        self.fail("only literals, singular queries and functions that give a value can be compared", at)

    def as_test(self, operand, at):
        if isinstance(operand, _Query) or (isinstance(operand, _Call) and operand.kind in ("logical", "nodes")):
            return operand
        if isinstance(operand, _Literal):
            self.fail("a literal on its own is not a test; compare it with something", at)
        self.fail(f"{operand.name}() gives a value, which is not a test; compare it with something", at)

    def number(self):
        """A number literal (section 2.3.5.1): an integer or -0, an optional fraction and exponent."""
        m = re.compile(r"(-?(?:0|[1-9][0-9]*))(\.[0-9]+)?([eE][-+]?[0-9]+)?").match(self.text, self.pos)
        if not m or (self.text[m.end() : m.end() + 1] in DIGITS and self.text[m.end() : m.end() + 1]):
            self.fail("not a number")
        self.pos = m.end()
        return float(m.group()) if m.group(2) or m.group(3) else int(m.group(1))


def _name_first(c):
    """ALPHA, _, or any character from U+0080 on (section 2.5.1.1)."""
    return c == "_" or ("a" <= c <= "z") or ("A" <= c <= "Z") or ord(c) >= 0x80

Checkpoint

Check: Translating queries and their traps

  1. Question 1 of 2 Why did orders[?city == "Pune"].id give [] in JMESPath?

    Choose one answer.

    Show the answer: Question 1 of 2

    Answer: Double quotes make "Pune" the name of a field, which no order has, so each city was compared with null

    JSONPath takes either quote, jq takes double quotes, and JMESPath reads double quotes as a field name. Check the quotes first when a translated filter finds nothing.

  2. Question 2 of 2 Which of these keep only BK-20002, whose coupon is null, and leave out the orders that have no coupon?

    Choose every answer that is right.

    Show the answer: Question 2 of 2

    Answer:

    • JSONPath: $.orders[?@.coupon == null].id
    • SQLite: WHERE json_type(doc, '$.coupon') = 'null'

    Only JSONPath's Nothing and SQLite's json_type() tell a missing member from a null one; jq, JMESPath and ->> do not.

Practise: city totals and order lines in SQLite

The two exercises put SQLite’s paths to work on a stationery shop’s orders: first the totals per city of the paid orders, then every order line with a quantity of 2 or more, which needs json_each(). Their sample tests create the table with their own orders and compare the rows your query returns:

Code lab · 2 steps

Query JSON orders in SQLite

Total the paid orders of each city, then list every order line of two or more, reading the JSON with ->> and json_each().

  1. Totals per city from JSON orders in SQLite To do
  2. Order lines of two or more, with json_each() To do

Exercise · Easy · SQL

Totals per city from JSON orders in SQLite

A stationery shop keeps each order as one JSON document in the column doc of the table orders:

{"id": "ST-501", "city": "Indore", "amount_paise": 12500, "status": "paid", "lines": [{"code": "PN-01", "qty": 5}]}

Write one query that returns, for the paid orders only, one row per city with three columns: city, the number of paid orders, and their total in paise. Sort the rows by paise, largest first, and by city when two totals are equal. Read the members with SQLite's ->> operator, which gives SQL values. The tests create the table with their own orders, run your query and compare the rows.

Starter code · city_totals.sql

-- One row per city: city, orders, paise, for the paid orders only.
SELECT doc ->> '$.city' AS city
FROM orders;
The sample tests · tests.yaml
# Sample tests: each one creates the orders table with its own JSON documents, runs your query and compares the rows
# it returns, in order.
tests:
  - name: totals the paid orders of each city, largest total first
    ordered: true
    setup: |
      CREATE TABLE orders (doc TEXT NOT NULL);
      INSERT INTO orders (doc) VALUES
        ('{"id": "ST-501", "city": "Indore", "amount_paise": 12500, "status": "paid", "lines": [{"code": "PN-01", "qty": 5}]}'),
        ('{"id": "ST-502", "city": "Pune", "amount_paise": 18000, "status": "paid", "lines": [{"code": "PN-02", "qty": 1}]}'),
        ('{"id": "ST-503", "city": "Pune", "amount_paise": 6000, "status": "cancelled", "lines": [{"code": "NB-10", "qty": 1}]}'),
        ('{"id": "ST-504", "city": "Kochi", "amount_paise": 9000, "status": "paid", "lines": [{"code": "IK-07", "qty": 2}]}'),
        ('{"id": "ST-505", "city": "Pune", "amount_paise": 64000, "status": "paid", "lines": [{"code": "NB-11", "qty": 2}]}'),
        ('{"id": "ST-506", "city": "Indore", "amount_paise": 2500, "status": "paid", "lines": [{"code": "PN-01", "qty": 1}]}');
    expected:
      columns: [city, orders, paise]
      rows:
        - ['Pune', 2, 82000]
        - ['Indore', 2, 15000]
        - ['Kochi', 1, 9000]
  - name: leaves out a city whose only order was cancelled, and breaks a tie by city
    ordered: true
    setup: |
      CREATE TABLE orders (doc TEXT NOT NULL);
      INSERT INTO orders (doc) VALUES
        ('{"id": "ST-601", "city": "Mysuru", "amount_paise": 4000, "status": "paid", "lines": [{"code": "PN-01", "qty": 2}]}'),
        ('{"id": "ST-602", "city": "Guwahati", "amount_paise": 9900, "status": "cancelled", "lines": [{"code": "NB-10", "qty": 1}]}'),
        ('{"id": "ST-603", "city": "Kochi", "amount_paise": 4000, "status": "paid", "lines": [{"code": "IK-07", "qty": 1}]}');
    expected:
      columns: [city, orders, paise]
      rows:
        - ['Kochi', 1, 4000]
        - ['Mysuru', 1, 4000]
A hint

doc ->> '$.city' reads the city as SQL text, and doc ->> '$.amount_paise' the amount as an SQL integer. Keep the paid orders with a WHERE on doc ->> '$.status', then GROUP BY the city and use count(*) and sum(…).

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Exercise · Medium · SQL

Order lines of two or more, with json_each()

The same orders table holds every order's lines in the array lines, each with a code and a qty. Write one query that returns a row for every line, of any order, whose qty is 2 or more, with the columns id (the order's id), code and qty, sorted by id and then by code. Turn the array into rows with json_each(orders.doc, '$.lines'), whose value column holds each line as JSON.

Starter code · big_lines.sql

-- One row per line with qty 2 or more: id, code, qty.
SELECT doc ->> '$.id' AS id
FROM orders;
The sample tests · tests.yaml
# Sample tests: each one creates the orders table with its own JSON documents, runs your query and compares the rows
# it returns, in order.
tests:
  - name: finds the lines with a quantity of 2 or more, in every order
    ordered: true
    setup: |
      CREATE TABLE orders (doc TEXT NOT NULL);
      INSERT INTO orders (doc) VALUES
        ('{"id": "ST-501", "city": "Indore", "amount_paise": 12500, "status": "paid", "lines": [{"code": "PN-01", "qty": 5}]}'),
        ('{"id": "ST-502", "city": "Pune", "amount_paise": 18000, "status": "paid", "lines": [{"code": "PN-02", "qty": 1}]}'),
        ('{"id": "ST-504", "city": "Kochi", "amount_paise": 9000, "status": "paid", "lines": [{"code": "IK-07", "qty": 2}]}'),
        ('{"id": "ST-505", "city": "Pune", "amount_paise": 64000, "status": "paid", "lines": [{"code": "NB-11", "qty": 2}]}');
    expected:
      columns: [id, code, qty]
      rows:
        - ['ST-501', 'PN-01', 5]
        - ['ST-504', 'IK-07', 2]
        - ['ST-505', 'NB-11', 2]
  - name: sorts several lines of one order by code, and skips the lines of 1
    ordered: true
    setup: |
      CREATE TABLE orders (doc TEXT NOT NULL);
      INSERT INTO orders (doc) VALUES
        ('{"id": "ST-701", "city": "Pune", "amount_paise": 30500, "status": "paid", "lines": [{"code": "NB-11", "qty": 2}, {"code": "IK-07", "qty": 3}, {"code": "PN-02", "qty": 1}]}'),
        ('{"id": "ST-700", "city": "Kochi", "amount_paise": 2500, "status": "paid", "lines": [{"code": "PN-01", "qty": 1}]}');
    expected:
      columns: [id, code, qty]
      rows:
        - ['ST-701', 'IK-07', 3]
        - ['ST-701', 'NB-11', 2]
A hint

Put json_each in the FROM clause next to the table, as FROM orders, json_each(orders.doc, '$.lines') AS line. Each row then has both doc and line.value, so line.value ->> '$.qty' is the quantity of that line.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

JSONPath & JMESPath Tester Its JMESPath mode runs an expression on your own document and builds the --query argument for a CLI.

The self-check covers the six ideas of this lesson, from choosing a language to the traps of translating. Your weakest idea leads, and a question you get wrong returns once more:

Practice that adapts to you

Practise this lesson

15 questions about 6 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.

  1. Question 1 of 15 Which of these can group values and total them with no other language around them?

    Choose every answer that is right.

    Show the answer: Question 1 of 15

    Answer:

    • jq
    • SQL with its JSON functions

    That is the main difference the four answers showed.

  2. Question 2 of 15 A script must turn a CLI's JSON output into a new document with one object per region and a count in each. Which fits best?

    Choose one answer.

    Show the answer: Question 2 of 15

    Answer: jq

    Grouping and a new shape together point to jq.

  3. Question 3 of 15 In a jq program, what does | do?

    Choose one answer.

    Show the answer: Question 3 of 15

    Answer: It passes every output of the filter on its left to the filter on its right

    A jq program is a pipeline of filters, each fed by the outputs of the one before.

  4. Question 4 of 15 In which order did group_by(.city) return the groups of the paid orders?

    Choose one answer.

    Show the answer: Question 4 of 15

    Answer: Sorted by city, from Guwahati to Pune

    The session printed Guwahati, Indore, Kochi, Mysuru and Pune.

  5. Question 5 of 15 Why pass the city as --arg city Kochi instead of pasting it into the jq program?

    Choose one answer.

    Show the answer: Question 5 of 15

    Answer: The value stays a string, so quotes or other characters in it can never change the program

    Put outside text into a query as a value, never as part of the query, in jq as in every query language.

  6. Question 6 of 15 What did orders[].items[].sku give?

    Choose one answer.

    Show the answer: Question 6 of 15

    Answer: One flat list of 10 skus

    The flatten operator [] merges sublists into one list.

  7. Question 7 of 15 Which of these is a string literal in a JMESPath expression?

    Choose one answer.

    Show the answer: Question 7 of 15

    Answer: 'Pune'

    The traps run showed "Pune" finding nothing and 'Pune' finding 3 orders.

  8. Question 8 of 15 Why did the JMESPath answer need one sum() for each city?

    Choose one answer.

    Show the answer: Question 8 of 15

    Answer: The specification's 26 functions have no group_by

    When the groups are not known in advance, use jq or SQL.

  9. Question 9 of 15 How does a SQLite path name the last element of the items array?

    Choose one answer.

    Show the answer: Question 9 of 15

    Answer: $.items[#-1]

    Each language names the last element its own way, which the translation table lists.

  10. Question 10 of 15 What does json_each(orders.doc, '$.items') produce in a FROM clause?

    Choose one answer.

    Show the answer: Question 10 of 15

    Answer: One row for every element of each order's items, with its value in the value column

    json_each() gives one row for each element of the array or object at the path and goes no deeper; json_tree() visits every level.

  11. Question 11 of 15 Which SQLite expression tells a coupon that is JSON null from a coupon that is not there?

    Choose one answer.

    Show the answer: Question 11 of 15

    Answer: json_type(doc, '$.coupon')

    SQL's NULL hides the difference that JSONPath keeps as Nothing against null.

  12. Question 12 of 15 In totals.py, what did JSONPath do, and what did Python do?

    Choose one answer.

    Show the answer: Question 12 of 15

    Answer: JSONPath selected the paid orders, and Python grouped them and added the amounts

    That division of work is typical whenever a JSONPath query feeds a report.

  13. Question 13 of 15 What total did every one of the four answers give for Pune, in paise?

    Type a number.

    Show the answer: Question 13 of 15

    Answer: 124600 paise

    Two paid orders, BK-20001 with 89700 and BK-20008 with 34900; the refunded BK-20003 was left out everywhere.

  14. Question 14 of 15 Which jq program asks the same as the JSONPath $.orders[?@.city == 'Pune'].id?

    Choose one answer.

    Show the answer: Question 14 of 15

    Answer: .orders[] | select(.city == "Pune") | .id

    Translate by meaning, then run both on the same data to compare.

  15. Question 15 of 15 What did .orders[0].items.sku do in jq?

    Choose one answer.

    Show the answer: Question 15 of 15

    Answer: It stopped with an error, because items is an array and .sku needs an object

    One query, four behaviours, which is why translations need a run on real data.

Interview questions

Warm-up (fresher to 2–5 years): jq, JMESPath or JSONPath: which can group orders by city and total them, and which can only select?

Only jq can do it on its own: it is a programming language of filters, and group_by(.city) with map(.amount_paise) | add gave the five city totals in one program. JMESPath can select, reshape and add up a list, but it has no group_by, so each city needs its own sum(orders[?city == 'Pune' && status == 'paid'].amount_paise), with the cities known in advance. JSONPath can only select: $.orders[?@.status == 'paid'] returns the paid orders, and the grouping and the sums are done in the language around it. When the JSON already lives in a database, its SQL paths read the values and GROUP BY does the rest. So choose by the job, not by habit.

Three harder questions start from this lesson: a CLI query that sometimes returns several ids, a total that lost its last digits in a jq step, and a join on a JSON column that matches nothing. Decide what you would check first, then open each first step:

Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.

  1. Mid to senior

    A deploy script picks one running server with aws ec2 describe-instances --output text and the query "Reservations[].Instances[?State.Name == 'running'].InstanceId | [0]". In a large account it sometimes prints several ids, and the next step breaks. Explain why, and make it print exactly one id every time.

    Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups

  2. Mid to senior

    A jq step adds a computed total to each record of a ledger and passes every other member through. Downstream, the 20-digit record ids still match, but the totals of very large counters are wrong in their last digits. Why does jq keep one number exactly and not the other, and how do you make the pipeline safe?

    Model answer, Pro. First step and follow-ups

  3. Fresher to mid

    A report joins a table of branches to orders stored as JSON in SQLite with ON b.city = o.doc -> '$.city', and it matches no rows, although every city is in both tables. Explain the empty result, fix the join, and say which other comparisons in the report are at risk.

    Model answer, Pro. First step and follow-ups

Flashcards · 7 cards

Recall it, then check

Say the answer to yourself before you look: recalling it is what makes it stick.

  1. What job does each of JSONPath, JMESPath, jq and SQL JSON paths do?

    JSONPath selects nodes, JMESPath selects and reshapes, jq computes anything, and SQL paths read values that SQL then groups and joins.

  2. Which of jq, JMESPath and JSONPath can group orders by city and add them up alone?

    jq, with group_by and add. JMESPath has no group_by and needs a sum() per city named in advance, and JSONPath only selects.

  3. What is a JMESPath projection?

    An expression such as orders[*] that applies the rest of the expression to every element and drops null results. A pipe ends the projection.

  4. How do SQLite's -> and ->> differ?

    -> returns JSON text, so a string keeps its quotes; ->> returns an SQL value: text, an integer, a real or NULL. json_each() turns an array into rows.

  5. What do PostgreSQL's SQL/JSON paths add to plain arrows?

    Filters such as ? (@.qty >= 2), last for the final position, and two modes: lax, the default, unwraps arrays and lets a step that does not fit find nothing, while strict makes the same mismatch an error.

  6. Does jq keep the digits of a large number?

    Only while it copies the literal through. Once it computes with the number, the value is a double.

  7. How do you translate a query from one JSON language into another?

    By meaning, not by look: they treat missing members, quotes, a name applied to an array and numbers differently. Then run both on the same data.

Key takeaways

  • JSONPath selects nodes, JMESPath selects and reshapes, jq computes anything, and SQL paths read values for SQL to group; choose by where the query runs and what the answer must be.
  • A jq program is a pipeline of filters: select(), map(), group_by() and add gave the city totals in one line, and jq keeps number literals exact only until it computes with them.
  • JMESPath’s projections apply the rest of an expression to every element and drop the nulls; a pipe stops a projection, raw strings take single quotes, and there is no group_by.
  • SQLite’s -> gives JSON text and ->> an SQL value, json_each() turns arrays into rows, and PostgreSQL’s SQL/JSON paths add filters, last, and lax and strict modes.
  • Queries that look alike differ in how they treat a missing member, quotes, a name applied to an array and numbers, so translate them by meaning, then run them on the same data.

Terms in this lesson

jq filter
A jq program, or a part of one: it takes one JSON input and produces zero, one or many outputs. Filters join with | into a pipeline, and each passes every output it produces to the next.
lax mode
The default mode of PostgreSQL's SQL/JSON paths: an array is unwrapped when a path asks for a member, and a step that does not fit the data matches nothing. In strict mode the same path raises an error.
projection
In JMESPath, an expression such as orders[*], orders[] or orders[?…] that applies the rest of the expression to every element of a list and leaves out the elements whose result is null.

References

Related tools

Report a problem with this lesson

Quick answers and tool search

Type to search tools or to get a quick answer, for example 18% of 2500. Use the up and down arrow keys to move through the results, Enter to choose, and Escape to close.