JSON, NDJSON & JSONB files

AddisDB reads JSON documents, JSON Lines (NDJSON) and JSONB files as tables. Nested structures are flattened into columns for the grid, and the JSON tab always has the original shape if you need it.

Who it is for

JSON is what APIs return and what log pipelines write. NDJSON — one object per line — is the format of choice for anything streamed, because it can be appended to and read line by line.

The reason it is awkward to analyze is that it carries structure but no schema: two records in the same file can disagree about whether a field exists, what type it is, or how deep it sits. Reading it as a table means resolving that, which is what the loader does by sampling.

Use this when you have an API dump, an export from a document database, or a day of application logs, and you want to count, group and filter it rather than scroll it.

Preparing the file

1. Both shapes work: a top-level array of objects, or one object per line (NDJSON / JSON Lines).
2. Keep the objects reasonably consistent — column detection samples the file, so wildly varying keys make for a sparse table.
3. Very deep nesting flattens into long column names; querying the nested value directly with SQL is often tidier.
4. A file whose top level is a single object wrapping the array — { "data": [ … ] } — reads as one row; unwrap it, or address the array in the query.
5. The file is opened read-only.

Open it in AddisDB

1. New Connection → Files → Common → JSON / NDJSON / JSONB.
2. Choose the file, name the connection, and Save.

Reaching into nested data

Arrow operators address a path inside a JSON value: -> returns JSON, ->> returns text. UNNEST turns an array into rows, which is how you go from one record per order to one record per line item.

-- Nested fields addressed directly
SELECT payload->>'$.customer.email'      AS email,
       payload->'$.items'                AS items
FROM   events
WHERE  payload->>'$.type' = 'order.created';

-- One row per element of an array
SELECT e.id, item.unnest AS item
FROM   events e, UNNEST(e.items) AS item;

When the keys vary between records, json_keys over a sample is the quickest way to find out what actually exists in the file before you write a query against a field that is only present in a tenth of the rows.

What AddisDB gives you

• SQL over semi-structured data, with nested fields addressable in the query.
• The JSON tab for the original document alongside the flattened grid.
• The Chart view for aggregations, and notebooks for analysis worth keeping.
• AI chat grounded in the file’s detected columns.
• ⌘K search across every connection, and a source file that is never modified.