Inspect JSON stored in a SQLite column

10 minutes · Free read-only mode

A table cell can contain a whole JSON object. Inspect its structure first, then extract only the field you need for a query.

1. Preview one JSON value

Choose orders and select the metadata cell for order 101. Press Space to open the cell preview. For valid complete JSON, inspect the formatted representation or JSON tree. You should find channel set to web and gift set to false. Close the preview to return to the grid.

This is a cell preview inside Petti. Finder Quick Look instead previews a database file’s metadata. JSON viewing does not require enabling the optional JSON Tools plugin.

2. Extract a field with SQL

Switch to Query and run the following read-only statement. The path $.channel selects the channel property at the top level of each JSON object.

SELECT id, json_extract(metadata, '$.channel') AS channel
FROM orders
WHERE json_valid(metadata)
ORDER BY id;

Expect web for 101, mobile for 102, mobile for 103, web for 104, web for 105, and mobile for 106. The original metadata text remains unchanged. You have projected one field into the result, not split or rewritten the stored objects.

3. Understand missing and invalid values

json_valid filters out malformed JSON text. A missing channel property or a JSON null produces an SQL NULL from this scalar extraction; it is different from an empty string. In your own data, decide whether those cases belong in the same reporting category before grouping them.

This fixture stores complete, valid JSON in TEXT. A compressed or binary payload is not automatically JSON just because an app uses it that way. Check its storage format and use the raw or hex preview when interpretation is uncertain.

4. Keep larger payloads bounded

Cell previews have size and parsing limits. A truncated preview is not the complete value, and failure to parse a prefix does not prove the stored JSON is invalid. Avoid copying giant payloads into every query result; extract a narrow field when possible.

If the query reports an unavailable JSON function, inspect the SQLite runtime in ⋯ → Database Info…. See SQLite’s JSON function reference for the precise SQL behavior. For editing a real application’s JSON, prefer its own validation and write path.

Updated October 28, 2026