JSON
The sq JSON drivers implement connectivity
for JSON, JSON Array, and JSON Lines data sources.
JSON is a document source and thus its data is ingested and cached.
Note also that a JSON source is read-only; you can’t insert values into the source.
Variants
JSON
The JSON (json) driver expects an array of JSON objects. For example:
[
{
"actor_id": 1,
"first_name": "PENELOPE",
"last_name": "GUINESS",
"last_update": "2020-06-11T02:50:54Z"
},
{
"actor_id": 2,
"first_name": "NICK",
"last_name": "WAHLBERG",
"last_update": "2020-06-11T02:50:54Z"
}
]
JSON Array
The JSON Array (jsona) driver expects newline-delimited lines of JSON array data. For example:
[1, "PENELOPE", "GUINESS", "2020-06-11T02:50:54Z"]
[2, "NICK", "WAHLBERG", "2020-06-11T02:50:54Z"]
JSON Lines
The JSON Lines (jsonl) driver expects newline-delimited lines, where each line is a JSON object.
For example:
{"actor_id": 1, "first_name": "PENELOPE", "last_name": "GUINESS", "last_update": "2020-06-11T02:50:54Z"}
{"actor_id": 2, "first_name": "NICK", "last_name": "WAHLBERG", "last_update": "2020-06-11T02:50:54Z"}
Monotable
sq considers JSON to be a monotable data source (unlike, say, a Postgres data source, which
obviously can have many tables). Like all other sq monotable sources,
the source’s data is accessed via the synthetic .data table. For example:
$ sq @actor_json.data
actor_id first_name last_name last_update
1 PENELOPE GUINESS 2020-02-15T06:59:28Z
2 NICK WAHLBERG 2020-02-15T06:59:28Z
Add source
When adding a JSON source via sq add, the location string is simply the filepath.
For example:
$ sq add ./actor.json
@actor_json json actor.json
You can also pass an absolute filepath (and, in fact, any relative path is expanded to
an absolute path when saved to sq’s config).
When adding a JSON source, sq will usually figure out if a file is json, jsona, or jsonl.
However, if necessary, you can explicitly specify the JSON variant when adding the source.
For example:
$ sq add --driver json ./actor.json
$ sq add --driver jsona ./actor.jsona
$ sq add --driver jsonl ./actor.jsonl
Nested data
When sq encounters nested JSON, it flattens the nested data, using underscores to
build the flattened field names. For example, given this data:
[
{
"actor_id": 1,
"first_name": "PENELOPE",
"last_name": "GUINESS",
"last_update": "2020-06-11T02:50:54Z",
"address": {
"city": "Galway",
"country": "Ireland"
}
},
{
"actor_id": 2,
"first_name": "NICK",
"last_name": "WAHLBERG",
"last_update": "2020-06-11T02:50:54Z"
}
]
sq will flatten address.city into an address_city field.
$ sq @actor_json.data
actor_id first_name last_name last_update address_city address_country
1 PENELOPE GUINESS 2020-06-11T02:50:54Z Galway Ireland
2 NICK WAHLBERG 2020-06-11T02:50:54Z NULL NULL
sq is not able to handle your JSON data,
please open a bug, attaching a sample JSON file.