READ_JSONL

READ_JSONL is a table function: it reads a JSON Lines file (one JSON object per line) directly by path, without registering it as a table in a catalog first. Use it in a FROM clause wherever a table name is expected.

Syntax

sql

FROM READ_JSONL(<path> [, ignore_errors => <boolean>]
                        [, infer_schema => <boolean>]
                        [, infer_sample_size => <integer>]
                        [, credentials => '<workspace>.<secret>'])

Parameters

<path> — single string literal giving the file path (or glob pattern matching multiple files) to read.
ignore_errors => <boolean>, default false — If false, a malformed JSON record fails the query. If true, malformed records are skipped instead.
infer_schema => <boolean>, default true — Whether to infer each column's type from the sampled values (see infer_sample_size).
infer_sample_size => <integer>, default 5 — Number of rows sampled to infer each column's type.
credentials => '<workspace>.<secret>' — read a private gs:// or s3:// path with a stored secret. A string literal naming the secret, qualified with its workspace; never the credential itself. Every file read must fall under the secret's SCOPE, and you need ALTER on the secret's whole workspace to use it.

Examples

Query a Single File

sql

SELECT *
  FROM READ_JSONL('data/events.jsonl');

Skip Malformed Records Instead of Failing

sql

SELECT *
  FROM READ_JSONL('data/events.jsonl', ignore_errors => true);

Query a Set of Files with a Glob

sql

SELECT *
  FROM READ_JSONL('data/events-*.jsonl');

Alias the Relation

sql

SELECT e.id, e.status
  FROM READ_JSONL('data/events.jsonl') AS e
 WHERE e.status = 'ok';

Read a Private Bucket with a Stored Secret

sql

SELECT *
  FROM READ_JSONL('gs://acme-exports/billing/2026-10/*.jsonl',
                  credentials => 'analytics.billing_reader');

Notes

Supported column types are INTEGER, FLOAT, BOOLEAN, VARCHAR, and NULL. A column whose values don't fit one of these (for example a nested array or object) is not currently supported.
Column names and types are inferred from the file's own content at query-plan time; there is no AS alias(col1, col2, ...) form to rename columns — use SELECT ... AS new_name instead. A plain relation alias (AS alias, no column list) is supported.
Standard filter and column pushdown apply: simple WHERE column op literal predicates and the columns your query actually references are pushed into the read.
A glob path (containing *, ?, or [) matches multiple files; their combined content is read as one relation. Every matched file's columns and inferred types must agree with the first file's — a file whose schema disagrees fails the query rather than silently producing mismatched or missing columns.
gs://bucket/object and s3://bucket/object paths are supported. Without credentials => they are always fetched anonymously (a public object is read; a private one fails with an error) — Opteryx never signs a request or uses platform credentials on your behalf for a path given to READ_JSONL, and never picks a secret from the path. Glob patterns are not supported on the anonymous path, because listing a bucket's contents needs a permission a public, unauthenticated read does not have. With credentials => the read is signed with the named secret, and globs work: every file the glob expands to is checked against the secret's SCOPE before anything is read. Use gs://, not gcs://.