SQL queries

The SQL tab of the query bar runs SQL over the records of the open document. The table is named doc: one row per record of an NDJSON file, or one row per element of a JSON array. Queries run in DuckDB, which reads the file from disk, so a query over a multi-gigabyte file does not load it into memory. Queries read the current version of the document, including edits that are not saved yet.

The table names, the functions JSON Digger adds or refuses, and useful DuckDB constructs are listed in Query syntax.

Choose what doc contains

A SQL query reads rows, so doc has to be something that has rows:

• An NDJSON file: doc has one row per line, and the chip reads doc = records.
• A JSON file whose root is an array: doc has one row per element.
• An array inside a document: right-click the array in the tree and choose Query Array with SQL. The scope chip next to the editor shows the array’s path, for example doc = $.items. To go back to the whole document, select the undo arrow on the chip (Query the document root instead).

A file whose root is an object cannot be queried directly. Select one of its arrays instead.

An array inside a JSON file can be queried once the file has finished indexing. Its first query takes longer, and the status line shows Copying the array… N%.

Plain text files and live streams cannot be queried.

Run a query

1. Press Ctrl+E, or choose Search → SQL Query…. The query bar opens on the SQL tab with the cursor in the editor.

2. Type a query, for example:

   SELECT status, count(*) AS n FROM doc GROUP BY ALL ORDER BY n DESC

   The editor completes the columns of doc and common DuckDB functions.

3. Press Ctrl+Enter or select Run. Enter alone adds a line.

The results appear in the grid below the editor. The status line shows the row count and how long the query took. Cancel stops a running query, and running another query cancels the one before it.

To step through the queries you ran before, press Ctrl+Up and Ctrl+Down in the editor. The Syntax reference button (question mark icon) opens a sheet of tables, constructs, examples, and tips; choosing an example puts it in the editor.

Find columns that appear late in the file

DuckDB decides the columns of doc from the first 20,480 rows. A key that first appears after those rows is not a column.

To include every key, turn on Every column next to the scope chip. The next query reads the whole file once to find the columns, and the status line shows Reading the whole file for its columns…. This read is slow on large files: about 37 seconds for 1 GB of NDJSON on one test machine (see Performance). The result is kept until the file changes, so later queries on the same file skip it.

Read the results

The grid shows up to 10,000 rows. When a query returns more, the status line says so, and Export every row still writes all of them. Values appear as follows:

• NULL is shown in its own style, distinct from the text "NULL".
• Nested objects and arrays are shown as compact JSON.
• A very long value is shortened, and the status line counts the shortened cells.

To work with the grid from the keyboard, select a cell and use the arrow keys to move. Ctrl+C copies the cell, Ctrl+Shift+C copies the row, and double-clicking a cell copies it. Drag the top edge of the grid to resize it.

Reveal a row in the document

To jump from a result row to the record it came from, query doc_rows instead of doc and keep its _row column. _row is the row’s position in doc, starting at 0.

SELECT _row, * FROM doc_rows WHERE price > 100

In the results, click a _row value, Alt+click any cell of the row, or press Enter on the selected row. The Tree view selects the record or element. In an NDJSON file, a record past the first 4,096 opens in the NDJSON view at its line instead.

On a 1 GB NDJSON file on one test machine, queries over doc_rows took about three times as long as the same queries over doc. Use it only when you need to reveal rows.

Export, copy, or open the results

The icon buttons after Run act on the result. Hover over a button to see its name.

• Export every row asks for a file name and writes every row the query returns, with no 10,000-row limit. The format follows the extension you choose: .csv (with a header row), .ndjson or .jsonl (one JSON object per row), or .parquet (column types kept). The status line shows the progress, with a Cancel button.
• Copy the rows as TSV copies the rows shown in the grid as tab-separated text, ready to paste into a spreadsheet.
• Open the rows as a JSON document opens the rows shown in the grid in a new tab as an array of objects.

An export cannot replace a file that is open in JSON Digger. A cancelled or failed export leaves an existing file unchanged.

Read-only queries

The SQL tab runs one read-only query at a time: statements that write, such as COPY or CREATE, are refused. To write results to a file, use Export every row. See Accepted statements.

A query can read other files by path, for example to join the document with another file. See Read other files.

Memory and large files

A query can use up to the SQL memory limit. When a sort or a large aggregation needs more, it uses temporary disk space. If a query still runs out of memory, JSON Digger reports it; aggregate more, select fewer columns, or add a LIMIT.

To change the limit, open Preferences, select Viewer, and set SQL memory limit (MiB). The limit applies to the next query.

For query times and memory use on 1 GB files, see Performance. For the numeric limits of the SQL tab, see Limits.