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.