Query JSON with JSONPath

{
  "orders": [
    { "id": "A-1001", "customer": "Ines", "total": 42.5, "status": "shipped" },
    { "id": "A-1002", "customer": "Marco", "total": 180, "status": "pending" },
    { "id": "A-1003", "customer": "Yuki", "total": 96.2, "status": "shipped" },
    { "id": "A-1004", "customer": "Priya", "total": 310.75, "status": "shipped" }
  ]
}

1/2
/orders/1/id
/orders/3/id

Searching for a word finds every place it appears. JSONPath answers a different kind of question: which orders cost more than a hundred, what is the email of every user, which items have no price. It describes a position in the structure rather than a piece of text, so a match means the same thing whether the document is ten lines long or ten thousand. This guide runs one such query against a short list of orders, then pulls the answer out as a document of its own. It belongs to the Find and query JSON guide, which covers the rest of the Find bar.

What the example shows

The document is a small export of four orders, each with an identifier, a customer, a total and a status. The goal is the identifier of every order whose total is over 100. Two of them qualify, Marco’s and Priya’s, and the query finds exactly those two however long the list grows. The counter on the bar reads 1/2, both matches are highlighted, and Enter and Shift+Enter step between them. The paths under the counter are the two matches written as JSON Pointers.

Reading the query

Read it from left to right. $ is the root of the document, and .orders steps into the array. The part in square brackets is a filter: ? introduces it, @ stands for the order being tested, and @.total > 100 keeps an order only when its total is greater than 100. The final .id then selects the identifier of each order that survived, rather than the whole order.

Drop the .id and the same filter matches the two orders themselves, at /orders/1 and /orders/3. The syntax follows RFC 9535, the standard that finally pinned JSONPath down, so the filter is written without the parentheses older tools wanted. Those still work if you are used to them: $.orders[?(@.total > 100)] finds the same two orders.

Take the matches with you

Highlighting answers the question, but often you want the answer as data. The Find bar’s Extract matches into the other pane button writes the matches into the right pane as a new document. With the query above, the right pane receives this:

{
  "orders": [
    {
      "id": "A-1002"
    },
    {
      "id": "A-1004"
    }
  ]
}

The shape around each match is kept, so you can still see that the identifiers came from orders, while everything that did not match is dropped and the array closes up. The status bar confirms it with Extracted 2 matches into the right pane. Because the two panes then hold different documents, the editor switches from Mirror to Compare, and it leaves the differences unhighlighted, since a subset of a document differs from it everywhere by definition.

More queries worth knowing

Wildcards, descendants and slices

$.orders[*].total selects every total. Two dots search at any depth, so $..id finds every id key however deeply it is nested. Indexes count from zero, a negative index counts from the end, and $.orders[-1].id is the last order’s identifier. A slice takes a range whose end is left out, so $.orders[0:2].id gives the first two identifiers.

Combining conditions

Join tests with && and ||. $.orders[[email protected] > 100 && @.status == 'shipped'].id leaves only Priya’s order, since Marco’s is still pending. Strings take single or double quotes. Four shorthand operators go beyond the standard because they are quicker to type in a one-line box: ^= for starts with, $= for ends with, contains, and =~ for a regular expression.

A test on its own

In Query mode a bare condition is a query too. Typing status == 'pending' matches every object whose status is pending, wherever it sits in the document, which is quicker than writing out the path when you only care about the condition.

When plain text search is enough

Query mode is not always the right tool. With { } off, the bar looks for text, and three switches shape the search. Match case, shown as Aa, stops a search for ines from finding Ines. Regular expression, shown as .*, reads the query as a pattern. The scope menu, labelled K·V, limits the search to keys, to values, or to both. That scope does nothing in Query mode, where the path already says exactly what to select, and the menu says so when you hover over it.

Filter the view, and what a mistake looks like

The funnel button, Filter to matches, hides everything that is not a match or on the way to one. It hides rows in the Tree, Table and Graph views, so it is unavailable only when every view of the document is Code, which has no rows to hide. Closing the Find bar with Esc always brings the whole document back, so a filter can never be left running unseen.

A query that cannot be read is not guessed at. Type $.orders[[email protected] >] and the counter is replaced by the reason, Expected a value or a field, with the input marked until the query is complete again.

Open the editor or the JSONPath Tester to query your own data, or read the next guide, JSONPath cheat sheet, which runs every construct on one document.