Query JSON documents

Capture a JSON endpoint using the same capture, schedule and pin resources as an HTML page. The response becomes a document in the shared corpus. Periplus does not assign a business schema, fetch subsequent API pages, or follow URLs found inside JSON.

Inspect the document

Join observations and content using capture_id. JSON has format = 'json'. A successful acquisition means the complete response was received; parse_status separately reports whether Periplus could interpret its JSON syntax.

SELECT c.capture_id, c.observed_at,
       d.format, d.parse_status, d.parse_error, d.text
FROM captures c
JOIN documents d USING (capture_id)
WHERE c.requested_url = ?
ORDER BY c.observed_at DESC
LIMIT 10

documents.text contains the UTF-8 JSON payload without reserialization: whitespace, member order, duplicate keys, escapes and numeric spelling remain. A leading UTF-8 BOM or recognized XSSI prefix ()]}' or while(1);) is removed for interpretation. The original response bytes, including framing, remain in the capture's WARC; document_id hashes those original bytes.

Malformed JSON stays inspectable with parse_status = 'error'. parse_error is invalid_json, invalid_encoding or depth_limit. Invalid UTF-8 is displayed with replacement characters; the archive preserves the bytes. The interpretation depth limit is 128 nested arrays/objects. document_mode is null for JSON.

Read fields

For a few fields from large feeds, use json_values to read values by path without scanning every description in documents.text.

SELECT capture_id, parent_index, array_indices, kind, raw
FROM json_values
WHERE capture_id = ? AND path = '["jobs",null,"id"]'
ORDER BY value_index

path is a compact JSON array of member names, with null for each array step. The root path is []; a top-level array's members have path [null]. array_indices gives actual zero-based positions, from outermost to innermost. Generate path parameters with json.dumps(parts, ensure_ascii=True, separators=(',', ':')); parameters also avoid SQL escaping for Unicode or unusual names.

Each row has a capture-local preorder value_index, its parent_index (null for the root), and its zero-based member_index within the parent. Join sibling fields on capture_id and parent_index. Duplicate object members keep separate rows, even when their paths match. A missing member has no row; explicit JSON null has kind = 'null' and raw = 'null'. Containers have kind = 'object' or 'array' and null raw. Empty containers still have their own rows.

Scalar raw contains the exact JSON token, including quotes, escapes and numeric spelling. A string can be decoded with JSONExtractString(raw); choose explicit numeric conversions appropriate to the question. These conversions read only the selected tokens. Keys containing dots, brackets or empty strings stay distinct.

Check documents.json_index_status for selected captures before interpreting missing field rows. ok means a complete index, parse_error means invalid JSON, and resource_limit means the 16 MiB metadata/scalar index budget was exceeded. An over-budget document keeps its lexical text and successful parse status, but has no partial index. HTML and XML have json_index_status = 'not_json'. Value identities are scoped to the queried parser/publication, like HTML node identities.

The full lexical payload remains available through documents.text and the following native extraction patterns.

Use native ClickHouse JSON functions. Paths below are successive key arguments; integer array indexes in these functions are one-based.

SELECT c.observed_at,
       JSONExtractRaw(d.text, 'meta', 'total') AS total,
       JSONExtractRaw(d.text, 'jobs', 1, 'id') AS first_posting
FROM captures c
JOIN documents d USING (capture_id)
WHERE c.requested_url = ?
  AND d.format = 'json' AND d.parse_status = 'ok'
ORDER BY c.observed_at DESC
LIMIT 10

JSONExtractRaw returns a JSON fragment, so a numeric identifier need not first be converted to a floating-point number. JSONExtractString decodes a string; JSONExtract with an explicit ClickHouse type performs a typed extraction. Choose the conversion appropriate to your question.

Native extraction functions can return defaults for missing paths or wrong types. Use JSONHas and JSONType when absence, JSON null and type differences matter. Periplus preserves duplicate keys but assigns them no extra extraction semantics: native function behavior applies. Raw extraction does not promise byte-identical fragments: the engine may normalize them, including quoting integers outside its native integer range. documents.text is the lexical source when a function's interpretation loses information.

Expand arrays

ARRAY JOIN produces a row for each array entry. An empty array produces no rows; use LEFT ARRAY JOIN if the parent observation must remain in the result.

SELECT c.capture_id, c.observed_at,
       JSONExtractRaw(job, 'internal_job_id') AS job_id,
       JSONExtractRaw(job, 'id') AS posting_id,
       JSONExtractString(job, 'first_published') AS first_published
FROM captures c
JOIN documents d USING (capture_id)
ARRAY JOIN JSONExtractArrayRaw(d.text, 'jobs') AS job
WHERE c.requested_url = ?
  AND d.format = 'json' AND d.parse_status = 'ok'
ORDER BY c.observed_at DESC, posting_id
LIMIT 100

For a top-level array, omit the key argument: JSONExtractArrayRaw(d.text). Returned entries are JSON text and can be passed to more native JSON functions. To retain array positions, expand arrayEnumerate alongside the array and explicitly order by the resulting position. SQL row order is otherwise unspecified.

Expand object members

JSONExtractKeysAndValuesRaw returns key/value tuples. Values remain JSON fragments.

SELECT c.capture_id,
       tupleElement(member, 1) AS key,
       tupleElement(member, 2) AS value
FROM captures c
JOIN documents d USING (capture_id)
ARRAY JOIN JSONExtractKeysAndValuesRaw(d.text) AS member
WHERE c.requested_url = ?
  AND d.format = 'json' AND d.parse_status = 'ok'
ORDER BY key
LIMIT 100

Search and changes

JSON is not included in prose search: documents.visible_text is empty, and search/search_matches do not index its keys or values. compare_captures compares HTML prose and rejects JSON. Select the fields you care about in SQL to compare observations. A response field named updated_at is source data; observed_at is Periplus's observation time.

JSON has a document root and one text node for the existing node-text machinery; json_values separately exposes JSON value structure. There are no JSON member elements or CSS selectors. node_text(capture_id, 0) returns the same payload as documents.text. links contains no inferred JSON URLs.

NDJSON is not supported. Array expansion and extraction use the normal SQL read, time, memory and response limits. LIMIT limits displayed rows, not necessarily the work needed to read and expand a document.

See the ClickHouse JSON functions and ARRAY JOIN reference for native signatures and semantics.