Reading JSON

API reference: .j: signatures, options, return values and errors.

Use .j.read to load JSON records as a table and .j.info to inspect their sampled schema. Loading a file covers sources, types and targets. For general JSON serialization and parsing, use .j.j and .j.k.

Query a file

select from reads a JSON file of records as a table. The browser REPL includes price.json, so this example runs there unchanged:

q)5#select from `:price.json
| symbol    | price        |
|           |              |
|-----------|--------------|
| "ETHBTC"  | "0.04691000" |
| "LTCBTC"  | "0.00128800" |
| "BNBBTC"  | "0.00943800" |
| "NEOBTC"  | "0.00024130" |
| "QTUMETH" | "0.00121400" |

Both columns load as strings, because the file quotes its prices.

Read records

An array of objects gives one row per object. Numeric forms are retained: 100 loads as a long, while 100.0 loads as a float.

q).j.read["[{\"sym\":\"AAPL\",\"qty\":100},{\"sym\":\"MSFT\",\"qty\":250}]";::;::;()!()]
| sym    | qty  |
|        | long |
|--------|------|
| "AAPL" | 100  |
| "MSFT" | 250  |

Replace the text with `:trades.json to read a file. An individual object gives a one-row table; other root shapes are described in the API reference.

Read JSON Lines

The default format detects a single document or JSON Lines. State the format when the input is required to contain one record per line:

q)src:"{\"sym\":\"AAPL\",\"qty\":100}\n{\"sym\":\"MSFT\",\"qty\":250}\n"
q).j.read[src;::;::;(enlist `format)!enlist `newline_delimited]
| sym    | qty  |
|        | long |
|--------|------|
| "AAPL" | 100  |
| "MSFT" | 250  |

Select records inside an envelope

An API response often wraps its records in a field such as results. Select that field with path, then apply types to the selected records:

q)src:"{\"status\":\"OK\",\"results\":[{\"sym\":\"AAPL\",\"px\":171.4}]}"
q).j.read[src;::;(enlist `sym)!enlist "s";(enlist `path)!enlist `results]
| sym    | px    |
| symbol | float |
|--------|-------|
| AAPL   | 171.4 |

Use a symbol list such as `data`items to descend through several fields. A path can also contain numeric array indices; see the API reference for accepted forms.

Missing fields

Records match by field name. A missing field receives its column’s null:

q).j.read["[{\"a\":1},{\"b\":2}]";::;::;()!()]
| a    | b    |
| long | long |
|------|------|
| 1    |      |
|      | 2    |

The column set and types come from the sample. A new field appearing after that sample can fail the load; increase sample_size when the early records are not representative. See Bad rows.

Work with nested records

Nested objects retain their structure. Extract a nested table using normal q column selection:

q)src:"[{\"id\":1,\"quote\":{\"px\":171.4,\"qty\":100}},{\"id\":2,\"quote\":{\"px\":402.3,\"qty\":250}}]"
q)t:.j.read[src;::;::;()!()]
q)t`quote
| px    | qty  |
| float | long |
|-------|------|
| 171.4 | 100  |
| 402.3 | 250  |

Use path when the desired table is inside a document envelope; use q indexing when working with nested values after loading.