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.
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:
Answer the questions above: the table then marks what fits your case and says why the rest does not.
| 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
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:
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:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
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:
{
"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:
"""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
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
{
"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
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:
-- 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
Runs on this device, in your browser. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs.
Your run, in this browser
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 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
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:
"""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
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
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
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 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
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
- 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’sselect(.coupon == null)count showed. SQLite’s->>gives NULL for both, whilejson_type()tells them apart. - Quotes. In JMESPath,
'Pune'is a string and"Pune"is the name of a field, soorders[?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.skuselected nothing in JSONPath, JMESPath gave null, jq stopped withCannot 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:
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
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().
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(…).
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
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.
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
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.
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.
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.
-
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.
-
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?
-
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.
Flashcards · 7 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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()andaddgave 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
- jq 1.8 Manual (jq project)
- JMESPath Specification (JMESPath)
- JEP 12: Raw String Literals (JMESPath) (JMESPath)
- Filtering output in the AWS CLI (the --query option) (Amazon Web Services)
- JSON Functions And Operators (SQLite) (SQLite)
- PostgreSQL 18: JSON Functions and Operators (the SQL/JSON path language) (The PostgreSQL Global Development Group)
- PostgreSQL 18: JSON Types (the jsonpath type) (The PostgreSQL Global Development Group)
- RFC 9535: JSONPath: Query Expressions for JSON (IETF)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress