Query syntax

Filter language

Filter queries test each object at the top of a JSON array, or the root object, and select the objects that match.

Fields

Field	Means
name	The member name
address.city	The member city of the object in address
items[0].sku	The sku of the first element of items
items.*.sku	The sku of any element of items
data["first name"]	A key with spaces or other special characters, in quotes

A field may start with $.. When a field reaches an array, the condition matches if any element matches. A field that the object does not have counts as null.

Operators

Operator	Matches when the field
=, !=	Equals, or does not equal, the value. Types must match: 30 does not equal "30".
>, <, >=, <=	Compares as a number. Strings that hold a number, such as "2024", compare too.
contains	Contains the text, ignoring case
startsWith, endsWith	Starts or ends with the text, ignoring case
~	Matches a regular expression, ignoring case

Values and logic

• Text: in double quotes, "Paris". Write \" for a quote inside the text.
• Numbers: 30, -1.5, 2e3.
• Literals: true, false, null.
• Logic: AND, OR, and NOT, with parentheses to group. NOT binds tightest, then AND, then OR. Keywords and operators like contains can be written in any case.

(status = "failed" OR retries >= 3) AND NOT customer.email endsWith "@example.com"

Errors

Message	Fix
expected a value (strings must be quoted: "text")	Put the text value in double quotes
unterminated string	Close the text with "
expected an operator after field	Add an operator such as = between field and value
expected a condition	Complete the condition after AND, OR, or NOT
expected ')'	Close the parenthesis

JSONPath

JSONPath queries follow RFC 9535.

Syntax	Selects
$	The root of the document
.key, ['key']	A member by name. Use the bracket form for keys with spaces.
[0], [-1]	An array element by position, from the start or from the end
[*], .*	Every element or member
..key	key at any depth
[0:10], [::2]	A slice of an array: start, end, step
[?@.price < 10]	The elements for which the condition is true; @ is the element

In a filter, compare with ==, !=, <, <=, >, >=, combine with && and ||, negate with !, and use these functions:

Function	Returns
length(@.name)	The length of a string, array, or object
count(@.tags[*])	The number of nodes a query selects
match(@.id, 'A-.*')	Whether the whole string matches a regular expression
search(@.id, 'A-')	Whether part of the string matches a regular expression
value(@..price)	The value of a query that selects one node

$.orders[?@.total > 100 && length(@.items) > 2].id

jq

The jq tab supports the jq language and its standard functions, such as select, map, group_by, to_entries, test, and @base64d. The jq manual describes the language.

These jq features are not available:

• input and inputs. On an NDJSON file, the query already runs once per record.
• Modules: import and include.
• $__loc__, input_filename, and command-line variables such as $ENV or --arg values.
• halt, halt_error, and env. A query that reaches one of them stops with an error, for example “halt is not available in JSON Digger”.

debug and stderr output their input unchanged and print nothing.

Examples:

Query	Outputs
.users[] | .name	The name of every user
[.orders[] | select(.total > 100)] | length	The number of orders over 100
.orders | group_by(.status) | map({status: .[0].status, n: length})	The number of orders per status
to_entries | map(select(.value == null)) | map(.key)	The keys whose value is null

SQL

SQL queries use DuckDB’s dialect of SQL; the DuckDB documentation describes the full language. This section lists the tables JSON Digger provides and the statements it accepts. To run a query, see SQL queries.

Tables

Every query can use two tables:

Table	Rows	Columns
doc	One per element of the array in scope, or one per line of an NDJSON file	One per key of the records, typed from the first 20,480 rows
doc_rows	The same rows as doc	The columns of doc, plus _row: the row’s position in doc, from 0

Queries over doc_rows are slower than the same queries over doc, so use it only when you need _row. A result that keeps _row can be revealed in the document.

The columns of doc follow the records:

• A nested object is a STRUCT. Read a field with a dot: meta.owner.name.
• A nested array is a list. unnest(tags) returns one row per element.
• A key that holds values of different types is a JSON column.
• A column whose name is a keyword or contains special characters needs double quotes: "group".
• In an NDJSON file, a line that is not valid JSON is a row with every column NULL. To skip those rows, filter on a key every record has, for example WHERE id IS NOT NULL.

Read other files

A query can read any file on the computer by its path, for example to join the document with another file. Write paths with forward slashes.

Function	Reads
read_json_auto('C:/data/other.json', format = 'array')	A JSON file whose root is an array
read_ndjson_auto('C:/logs/app.ndjson', ignore_errors = true)	An NDJSON file
read_parquet('C:/data/events.parquet')	A Parquet file

The following query lists the ids of the document that another file does not contain:

SELECT a.id FROM doc a
ANTI JOIN read_json_auto('C:/data/other.json', format = 'array') b ON a.id = b.id

Useful DuckDB constructs

These DuckDB constructs suit JSON records:

Construct	Result
SUMMARIZE doc	Every column’s type, range, null percentage, and distinct count
DESCRIBE doc	Column names and types
GROUP BY ALL	Groups by every column that is not aggregated
quantile_cont(x, 0.95), median(x)	Percentiles and the median
date_trunc('day', ts)	A timestamp truncated to the day, hour, month, or another unit
HAVING count(*) > 100	Keeps the groups whose aggregate passes the condition

For example, this query counts the records per day:

SELECT date_trunc('day', CAST(ts AS TIMESTAMP)) AS day, count(*)
FROM doc GROUP BY ALL ORDER BY day

Accepted statements

The SQL tab runs exactly one statement that reads data: SELECT, WITH, FROM doc (DuckDB’s FROM-first form), VALUES, DESCRIBE, SHOW, SUMMARIZE, PIVOT with an IN list, or EXPLAIN and EXPLAIN ANALYZE of one of them. A trailing semicolon and comments are allowed. Everything else is refused before it runs:

Refused	Message
Any other statement, such as COPY, CREATE, INSERT, SET, ATTACH, INSTALL, LOAD, PRAGMA, or CALL	Only queries can run here. Use Export to write a file.
Two or more statements	Run one query at a time.
Functions that change the database or run SQL text, such as query, query_table, or checkpoint, and DuckDB’s logging and profiling functions	name is not allowed in a query.
The maximum_object_size parameter of the JSON readers	maximum_object_size cannot be raised here: it would lift the memory limit.
PIVOT without an IN (…) list of values	List the pivot values with IN (…): a PIVOT without them cannot run here.

To write results to a file, use Export every row in the SQL tab. It writes CSV, NDJSON, or Parquet.