trdsql jq syntax
Up to now, the JSON that trdsql targets has been flat JSON. Therefore, the object at the top level was the target of SQL.
[
{"age": "26", "name": "Tanaka"},
{"age": "32", "name": "Suzuki"}
]
JSON can be nested, so there are cases where you want to target a lower level in SQL. You can access it using SQL functions, but it’s a bit cumbersome.
{
"list": [
{"age": "26", "name": "Tanaka"},
{"age": "32", "name": "Suzuki"}
]
}
With trdsql, you can first process JSON using jq
syntax and then execute SQL on the result. The jq
syntax is written after the file name with “::” appended. Since it is jq
syntax, it mainly starts with a “.” dot and accesses the values inside.
(When targeting JSON, the entire array is interpreted as a single column).
trdsql "SELECT * FROM example0-s.json"
"[{""age"":""26"",""name"":""Tanaka""},{""age"":""32"",""name"":""Suzuki""}]"
(Interpret list as a table and put the objects inside as columns).
trdsql "SELECT * FROM example0-s.json::.list
26,Tanaka
32,Suzuki
When you want to access a deep level, you can easily access it by connecting with dots in the jq syntax like .list.menu.item
. Even if there is an array in the middle of the hierarchy, you can access it if you write the jq syntax (enclose it in "
, etc.).
trdsql "SELECT * FROM example0-s.json::\".list[1].name\""
Writing complex aggregations in jq
is difficult, so by converting the target into a table, you can perform aggregations in SQL. It is also easy to convert to CSV
or other formats with trdsql.