JSONPath JMESPath and SQL Path Expressions Compared

Every system that stores or moves JSON has grown a way to say "the third author's email" or "all the prices under ten". The ways look alike, since most of them borrow from XPath, and that resemblance is the trap. A path that works in one place returns an empty result, an error or a different answer in the next, and nothing in the syntax warns you. There are at least six dialects in daily use: the standard JSONPath of RFC 9535, the older JSONPath libraries that predate it, JMESPath, jq's filter language, the SQL/JSON path of PostgreSQL, and the smaller path strings of MySQL and SQLite.

This guide compares them on one document and one list of tasks, so each claim is something I ran. It covers syntax, semantics (where the dialects disagree about nothing, about null and about types), speed and indexes, safety, and how to write queries that survive a move between tools. The shorter articles linked from each section go through one tool in depth.

Six families, three jobs

It helps to separate what the tools are for.

• JSONPath selects values by a path. Its standard (RFC 9535, February 2024) is new; most libraries predate it and follow their own dialect or an informal 2007 article. It returns a list of matches, and can report where each match is.

• JMESPath is a smaller language with a published grammar and a compliance suite. It selects, filters, flattens, builds new objects and has a fixed library of functions. The AWS CLI uses it for --query.

• jq is a full programming language for JSON with a command line tool. It can select, but its strength is computing: group, reduce, arithmetic, string and date handling.

• PostgreSQL's SQL/JSON path is the SQL standard's path language, embedded in jsonb_path_* functions and the @? and @@ operators. It is close to JSONPath in spirit and different in syntax and in its three-valued logic.

• MySQL's JSON paths are strings given to JSON_EXTRACT, -> and friends: a subset with wildcards and ranges, no filters.

• SQLite's JSON paths are a smaller subset still: members and indexes, no wildcards in the version I tested.

Three jobs recur. Extract a value you know the place of. Search for values by a condition. Transform the result into a new shape or number. The first is easy everywhere. The second is where dialects diverge. The third is where the three first tools stop being alike: jq computes, JMESPath transforms within limits, JSONPath only selects, and the database dialects hand the rest to SQL.

The same tasks in six tools

I ran ten tasks in each tool that could do them. Outputs matched wherever the tool could answer; the interest is in the syntax and in what failed.

task: titles of all books
  jq          [.store.books[].title]
  JMESPath    store.books[].title
  JSONPath    $.store.books[*].title
  PostgreSQL  jsonb_path_query(doc, '$.store.books[*].title')
  MySQL       JSON_EXTRACT(d, '$.store.books[*].title')
  SQLite      (no wildcard) select value from json_each(d, '$.store.books') ...

task: the last book
  jq          .store.books[-1].title
  JMESPath    store.books[-1].title
  JSONPath    $.store.books[-1].title                        (RFC 9535)
  PostgreSQL  $.store.books[last].title                      ([-1] returned an empty array)
  MySQL       $.store.books[last].title                      ([-1] raised error 3143, invalid path)
  SQLite      $.store.books[#-1].title

task: titles of books under 10
  jq          [.store.books[]|select(.price<10)|.title]
  JMESPath    store.books[?price < `10`].title
  JSONPath    $.store.books[[email protected] < 10].title             (or [?(@.price < 10)])
  PostgreSQL  $.store.books[*] ? (@.price < 10).title
  MySQL       no filter in the path; JSON_TABLE(d, '$.store.books[*]' COLUMNS (...)) with WHERE price < 10
  SQLite      no filter in the path; json_each(d, '$.store.books') with WHERE json_extract(value, '$.price') < 10

All four that could filter returned Pocket Regex; MySQL's JSON_TABLE and SQLite's json_each routes returned it too, and both are the correct answer to the question in a database where the result is meant to be rows anyway.

task: every price at any depth
  jq          [..|.price?|numbers]
  JMESPath    (no recursive descent)
  JSONPath    $..price                    [12.5, 28, 35.99, 8, 4.5]
  PostgreSQL  strict $.**.price           (lax mode returned nine values, see below)
  MySQL       $**.price                   [12.5, 28, 35.99, 8, 4.5]
  SQLite      json_tree(d) WHERE key = 'price'   (the path language has no descent in 3.40)

task: total, group, arithmetic
  jq          [.store.books[].price]|add      group_by(.author)|map({author:.[0].author,n:length})
  JMESPath    sum(store.books[].price)        (no group_by, no arithmetic)
  JSONPath    (not possible)
  PostgreSQL  path: arithmetic on one value only; use SQL sum() over jsonb_path_query rows
  MySQL / SQLite   SQL aggregate over JSON_TABLE / json_each rows

The sum of the four prices was 84.49000000000001 in jq and JMESPath, and 84.49 in PostgreSQL, which sums numeric and not floating point. Whatever the tool, round money.

Which constructs exist where

                      jq        JMESPath   JSONPath(RFC)   PostgreSQL   MySQL 8.4    SQLite 3.40
negative index        [-1]      [-1]       [-1]            no: [last]   no: [last]   no: [#-1]
slice / range         [1:3]     [1:3]      [1:3]           [1 to 2]     [1 to 2]     no
wildcard              [] / .[]  [*]        [*]  .*         [*]  .*      [*]  .*      no
recursive descent     ..        no         ..              .**          **           no (json_tree)
filter in the path    select()  [?...]     [?...]          ? (...)      no           no
regex in the filter   test()    no         match, search   like_regex   no           no
functions             many      ~25        5               methods      none         none
arithmetic            yes       no         no              single value no           no
reports locations     paths()   no         yes             no           JSON_SEARCH  json_tree

Read one thing from that table above all: **only the first three agree on [-1]**, and only PostgreSQL, MySQL and SQLite insist on last, with three different spellings of "the end".

Semantics: what the tools do about nothing

This is where portable-looking queries break, and the failures are quiet.

A missing path. jq's .nothing is null, JMESPath's nothing is null, MySQL's JSON_EXTRACT(d, '$.nothing') is SQL NULL and SQLite's json_extract is NULL. JSONPath returns an empty list. PostgreSQL's default lax mode returns an empty result too, while strict mode raises JSON object does not contain key "nothing". So "no value" is spelled null, [], NULL or an exception depending on where the query runs, and code that checks if result is None against a JSONPath library will believe a missing field is present.

Null versus absent. In jq and JMESPath a member holding null and a missing member both read as null. JSONPath keeps them apart: a null member comes back as [null], a missing one as []. In filters, RFC 9535 says @.note == null is true only for a present null, while JavaScript-based libraries use loose equality in which a missing member (undefined) also equals null: on four test items jsonpath-plus matched three where the RFC libraries matched one.

Existence versus truthiness. In RFC 9535 a bare [[email protected]] tests that the member exists. An item with active: false is included. JavaScript libraries treat it as truthiness and drop it. The same filter returned ids 1, 2, 4 in the standard-following Python libraries and 1, 4 in jsonpath-plus.

Types in comparisons. Compare the number 10 with the string "10". RFC 9535: not equal, and == '10' matches the string. JavaScript's ==: equal. PostgreSQL: the comparison is unknown, which a filter treats as false, with no error, and is unknown can find those rows. Python's JMESPath package: ordering a number against a raw string raised TypeError (the specification says null). Data from sources that sometimes quote numbers will silently drop out of numeric filters in most of these, and the only way to notice is a test that expects a match.

Lax mode and automatic unwrapping (PostgreSQL). In lax mode $.store.books.title works on an array by applying the accessor to every element. That convenience has a cost in recursion: $.**.price visited the books both as array elements and unwrapped, returning the four book prices twice (nine values for five prices). Strict mode returned the correct five. This one has no analogue in the other dialects.

Arrays of results. JSONPath and PostgreSQL's jsonb_path_query_array always return a list, even for a single match. jq emits zero or more separate outputs (wrap in [ ... ] to collect). JMESPath returns the value or a list depending on whether the expression projects. MySQL's JSON_EXTRACT with a wildcard returns a JSON array, and without one the scalar.

The syntax of filters

Filters are where dialects drift most, and the divergences cost the most to find.

• Parentheses. Older JSONPath: [?(@.price < 10)]. RFC 9535: [[email protected] < 10], with parentheses allowed as grouping. Tested on five libraries, the parenthesized form returned ["Pocket Regex"] in all five. The RFC form returned it in three Python libraries, returned an empty list with no error in jsonpath-plus, and raised a lexical error in jsonpath.

• PostgreSQL puts the filter after the path it filters: $.store.books[*] ? (@.price < 10).title, with a space and a question mark, and == and && as operators.

• JMESPath needs backticks around number literals: [?price < 10]. A bare 10 is a parse error.

• jq has no filter syntax at all, only the select(condition) function, which is why it composes so well.

Functions in filters differ too. RFC 9535 has length, count, match, search and value; count(@.tags) > 1 matched nothing because @.tags is one node, and length(@.tags) > 1 matched two books. PostgreSQL has .size(), .type(), .double(), .ceiling(), exists() and like_regex. JMESPath has contains, starts_with, length and dozens more. Regular expressions are a case of their own: the I-Regexp dialect of RFC 9535 does not take inline flags, so match(@.name, '(?i)ana') quietly matched nothing in both Python libraries that implement the function.

JSONPath syntax for everyday queries and filtering arrays by value with JSONPath have the full lists, including the existence test, null and the missing-member crash in jsonpath-plus (Cannot read properties of undefined).

A portable subset

If a query must run in more than one JSONPath library, which constructs hold? I ran a list across five of them (jsonpath-rfc9535, python-jsonpath, jsonpath-ng, jsonpath-plus, jsonpath) and kept what returned identical results in all:

$.store.books[*].title              child names and the wildcard
$.store.books[0].title              a non-negative index
$['store']['magazine']['title']     bracket notation
$.store.books[1:3].title            a slice
$.store.books[::2].title            a slice with a step
$..price                            recursive descent for a name
$.store.books[0,2].title            a union of indexes
$.store.books[?(@.price < 10)].title    the parenthesized filter, simple comparisons

Not on the list, because at least one library failed or differed: negative indexes ([-1] returned [] in both Node libraries), the RFC filter without parentheses, && with member access on values that may be missing, regexes, count and length, and any bare existence test. For a query that has to be shared, restrict yourself to the list and test it against every library with a document that has awkward items. For one you control, pick one library, pin its version, and use its full syntax.

Where each one runs

• AWS CLI: --query takes JMESPath, applied by the CLI after the response has arrived. Server-side filtering is a separate --filters option, so the query narrows output without reducing what AWS sends (JMESPath queries for the AWS CLI and Python).

• Shell scripts and CI: jq, because it is installed nearly everywhere and handles JSON Lines.

• Application code: a JSONPath or JMESPath library for paths that come from configuration, and plain code for paths that are fixed (using JSONPath in Python).

• Inside the database: the database's own dialect, because it can use indexes and avoids moving the document (PostgreSQL SQL JSON path expressions).

For the database dialects the practical rules are these. PostgreSQL has the richest: filters, variables ($max passed as jsonb), methods, regex and, with a GIN jsonb_path_ops index, fast @? and @@ lookups. MySQL's paths select; to filter, use JSON_TABLE (its output is rows, and a WHERE then filters), JSON_SEARCH to find the path of a string ("$.store.books[2].title" for SQL Tuning) and MEMBER OF for array membership. SQLite 3.40 handles named members and indexes, [#-1] for the end, -> and ->>, and uses json_each and json_tree for everything else; the version matters, as later releases changed the JSON functions. For turning arrays into rows in each engine, see JSON_TABLE in PostgreSQL 17, JSON_TABLE in MySQL and SQLite's json_extract.

Speed and indexes

A two-condition filter (price < 5 and owner.name == 'Ana') over 100,000 items, 1,584 matches, same ids from every tool:

list comprehension in Python     0.006 s
jq 1.8.2 CLI                     0.27 s in total, of which 0.21 s is reading the file
JMESPath (Python, compiled)      0.170 s
jsonpath-ng                      0.231 s
JSONPath (rfc9535)               0.274 s
jsonpath-plus (Node)             73 ms
python-jsonpath                  1.267 s

The path tools cost 30 to 200 times what a plain loop costs. That is fine for a configuration file or an API response, and a problem in a loop over millions of nodes. The difference between the tools is smaller than the difference between any of them and a loop.

The database dialects change the picture because they can be indexed. On a table of 1,000,000 jsonb documents (261 MB), doc @@ '$.id == 999990' took 140.6 ms with a sequential scan and 0.2 ms with a GIN jsonb_path_ops index (86 MB, built in 2.8 s). A range ($.id > 999990) was not served by the index (95.7 ms), and the function form jsonb_path_exists(...) planned a sequential scan where the @? operator used the index. A path language that lives in the database is the only one of these that can answer from an index.

One more difference is how they read input. jq reads a stream of documents, so a file of JSON Lines is processed one record at a time with a flat 2 MB of memory, and --stream handles a single document too large for memory. The path libraries take one parsed document, which means the whole file is loaded first, at roughly eight times its size in Python. A path query over a multi-gigabyte file is therefore a job for jq, DuckDB or a streaming parser, and not for a path library (why loading a large JSON file eats all your memory).

Safety

A path or a filter is code. A few concerns follow.

• Evaluation. Some JavaScript JSONPath libraries compile filter expressions with a JavaScript engine. In my tests jsonpath-plus 11.2.1 and jsonpath 1.3.0 refused attempts to assign to a global, to reference process or to reach the Function constructor, but jsonpath-plus has an eval option whose "native" value hands the filter to JavaScript, and false disables filters entirely. Do not accept path expressions from untrusted users with any library that evaluates scripts.

• Parameterization. Do not concatenate user values into a path. PostgreSQL takes variables as the third argument ($max), and the other tools should compare in code after the query.

• Cost. Recursive descent on a large document visits everything. .. on a 100,000-item array took 0.165 s in the fastest library and 1.746 s in another. Put a limit on user-supplied paths if they exist at all.

• Information. Error messages from some libraries and databases print part of the document (member 'B' not found in {'A': '1'}), so do not forward them to clients.

Choosing

you want to                                              use
read a value or two in a shell script                    jq
compute a total, group, join or reshape                  jq, or SQL after loading the data
share one query across Python, Go, JS and the AWS CLI    JMESPath
select with a path that may come from configuration      JSONPath (RFC 9535 library, pinned)
find where a value lives, or patch it afterwards         JSONPath, then JSON Pointer
query a column and use an index                          the database's own path language
filter in MySQL or SQLite                                JSON_TABLE / json_each and WHERE
turn JSON into tables for analysis                       load into SQL, query there

The deeper comparison of the three general-purpose tools, with eleven tasks and timings, is in choosing between jq, JSONPath and JMESPath. When the work is analysis and not lookup, it is usually better to turn the documents into rows once (analyzing JSON data with SQL).

Truthiness: three answers to "is it there?"

The same small question, "which books have stock", shows how many ways a language can interpret a bare value. The stock of the four books is 4, 0, 11 and 2. Asked as a bare test:

RFC 9535  [[email protected]]              all four books       (the member exists)
jsonpath-plus  [?(@.stock)]       three books          (JavaScript truthiness: 0 is false)
jq  select(.stock)                all four books       (only null and false are falsy in jq)

Three tools, two answers, and the intent was probably "stock above zero". In jq a number is always truthy, so select(.stock) keeps the zero, the same as the standard JSONPath; in JavaScript 0 is false, so the book with no stock vanishes. The reliable form is the one that says what it means: [[email protected] > 0] in JSONPath, select(.stock > 0) in jq, [?stock > 0] in JMESPath, ? (@.stock > 0) in PostgreSQL. A habit worth keeping everywhere: write the comparison, never the bare value.

The same applies to null. select(.stock != null) in jq returned all four books, because none has a null stock, while a filter on a field that is present and null behaves differently in each tool, as the last section showed. When the data can contain nulls, put a test with a null in the fixtures.

Testing queries across dialects

If a path expression is part of a product (a configuration option, a mapping in a settings file, a saved query), it should have tests, and the tests should be written against the dialect the production code runs. A small harness is enough. Keep one JSON fixture with the awkward items from this guide: an item whose boolean is false, one with an explicit null, one where the member is missing, one where a number is stored as a string, one with an empty array, one where a nested object is absent. Keep a table of queries and the result you expect from each on that fixture. Run the table against the library, and again after every upgrade of the library, as part of the build.

The cases that caught me in the preceding sections were all of that kind. The parenthesis-free filter that matched nothing in one library, the [-1] that returned an empty array in two Node libraries and PostgreSQL, the nested filter that crashed on the first item without an owner, the regex flag that silently matched nothing, the lax-mode recursion that duplicated values, the comparison between a number and a string that was neither true nor false. None of them raised an error that a test with a clean fixture would see. Every one returned an answer that looked plausible. Only a fixture built to disagree with a naive reading of the query shows them.

A side benefit is that the fixture table documents the intended meaning. When someone changes a library or a path, a test that fails with the old and new result next to each other is a short conversation (finding what changed between two API responses shows how to produce that comparison as a path-level report).

From a query to a change

Selecting is often the first half of a job whose second half is a change: set every out-of-stock book's price to zero, redact every email, bump every version. Of the tools here, only JSONPath tells you where the matches live, and that is what makes the second half safe. In the test, python-jsonpath found the out-of-stock books' prices and returned their JSON Pointers; building a JSON Patch from them and applying it:

pointers:   ['/store/books/1/price']
result:     prices [12.5, 0, 35.99, 8]      original document still [12.5, 28, 35.99, 8]

The pointer worked unchanged in a different library, jsonpatch, which is the point of pointers: they are a standard, so the finder and the changer need not know each other (what JSON Patch is and how RFC 6902 works, applying a JSON Patch in Python and JavaScript). Doing the same with jq means writing the update in jq (map(if .stock == 0 then .price = 0 else . end)), which is clear for a one-off and awkward to review later. With JMESPath there is no way to change anything at all, since it only reads. When the next step is a change, prefer the tool that returns locations, and let a patch library do the writing, because it brings checks (atomic apply, a test operation, errors for missing paths) that a hand-rolled loop does not.

What is standardized, and what is not

It helps to know which parts of this field are settled. JSONPath is standardized as RFC 9535 (2024), and a compliance test suite exists for implementations. Before that, behavior followed an informal description from 2007 and each library's choices, which is why the old Node libraries and the new Python ones differ on things the standard now decides: filter syntax, negative indexes, existence semantics, the function library. Libraries are moving toward the RFC at their own speed, and the changelog of the library you use is the document to read before an upgrade.

JMESPath has a published specification and a shared set of compliance tests, and yet the implementations still differ at the edges, as the number-against-string comparison showed. PostgreSQL's SQL/JSON path follows the SQL standard's design, with PostgreSQL-specific extensions, and the manual for the major version in use is the authority. MySQL and SQLite define their own paths in their manuals, with the SQLite behavior depending on the version compiled into the library (the 3.40 build I used lacks wildcards, which newer ones may not). jq's behavior is defined by its manual and its implementation; a Go reimplementation exists and differs in small details.

The practical consequence: name the implementation and version in every place a query is stored. A comment such as "JSONPath, jsonpath-rfc9535 2.0" next to a configured path saves the next maintainer an afternoon.

When a query breaks in production

Three situations account for most failures. A library upgrade changed an edge case, so the same query returns different data: the fixture table finds it. A new kind of data arrived, such as a quoted number, a missing member or a null where there used to be a value: validate input with a schema, so that the surprise is an error at the boundary and not a silent drop in a filter. Or the query was copied from an example written for another dialect: translate it with the steps below and test it on the fixture.

When you debug, reduce the problem. Print the document at the point of the query, run the path on the smallest document that shows the problem, then try the same path in a second tool. If two tools disagree, the disagreement is often the explanation: one is following the standard and the other an older convention, or one evaluates truthiness and the other existence. Read the library's documentation for the specific construct, not its general description, and check the version of the library that is actually installed in the failing environment, which is frequently not the one on your laptop.

Moving a query between dialects

A query written for one dialect translates in a few mechanical steps. The same question, "titles of books under ten", from JSONPath to the others:

• To PostgreSQL: move the filter to follow the path it filters and replace [?...] with [*] ? (...): $.store.books[*] ? (@.price < 10).title. Replace any [-1] with [last]. Use strict mode if you use .**.

• To MySQL: there is no filter; use JSON_TABLE(d, '$.store.books[*]' COLUMNS (title VARCHAR(50) PATH '$.title', price DECIMAL(6,2) PATH '$.price')) t WHERE t.price < 10. Replace [-1] with [last].

• To SQLite: select json_extract(value, '$.title') from json_each(d, '$.store.books') where json_extract(value, '$.price') < 10. Replace [-1] with [#-1].

• To JMESPath: store.books[?price < 10].title: no $, no @, backticks around the number.

• To jq: [.store.books[]|select(.price<10)|.title].

After translating, test with a document that has the awkward cases: a false, a null, a missing member, a number stored as a string, an empty array. Most of the silent differences above appear there, and nowhere else.

When a path expression is not the answer

The question needs a shape the data does not have. If every query starts with the same five-step path, flatten the documents into a table and query the table (flattening nested JSON).

The structure is yours to design. A path is a workaround for data that is awkward to reach. If you control the producer, make the field easy to reach.

The data is large. All these tools read a whole document. For gigabyte files use streaming, DuckDB or line-oriented tools (handling JSON files too large to parse).

Correctness depends on exact semantics. Validation belongs to a schema, which says what must be present and of which type; a path language just reports what it finds.

The standards and manuals behind this guide are RFC 9535 for JSONPath, the JMESPath specification, the jq manual, the PostgreSQL SQL/JSON path chapter, the MySQL JSON path syntax and the SQLite JSON functions. To look at a document before writing a path, use the JSON formatter.