jq コマンドで JSON を CSV に変換する
jq command supports conversion from JSON to CSV
JSON全盛のWeb界隈。しかし、JSONの特定の値を抜き出してExcelにまとめたい事がありますよね。今日はそんな願いを1 linerで実現する方法を紹介します。
環境
- macOS Sierra
- fish shell v2.6.0
基本的な方針
1 liner といいつつ、実質利用するコマンドは jq
コマンドのみです。
とっても優秀な jq
コマンドは、ご丁寧にフィルターで加工した値をCSV形式で出力する機能を用意してくれています。
> jq -r '<CSV 形式で出力したい要素を取り出すフィルター> | @csv'
最期の|@csv
が重要です。
簡単な例を使って、振る舞いを確認してみます。
出力(1) サンプルデータ(sample1.json
)の内容の確認
> cat sample1.json
[
{"key1": "value11", "key2": "value12", "key3": "value13"},
{"key1": "value21", "key2": "value22", "key3": "value23"},
{"key1": "value31", "key2": "value32", "key3": "value33"},
{"key1": "value41", "key2": "value42", "key3": "value43"}
]
配列の各要素が、複数の <key>:<value>
というメンバーを持つオブジェクトになっています。
出力(2)|@csv
無しで実行
> cat sample1.json | jq -r '.[] | [.key1, .key2, .key3]'
[
"value11",
"value12",
"value13"
]
[
"value21",
"value22",
"value23"
]
[
"value31",
"value32",
"value33"
]
[
"value41",
"value42",
"value43"
]
フィルターの書き方については、後で解説します。今は、とりあえず、 sample1.json
の <value>
を配列の要素として抜き出している所だけ着目してください。
出力(3)|@csv
付きで実行
> cat sample1.json | jq -r '.[] | [.key1, .key2, .key3] | @csv'
"value11", "value12", "value13"
"value21", "value22", "value23"
"value31", "value32", "value33"
"value41", "value42", "value43"
はい、 <value>
のみを抜きだしたCSVになりました。
一見簡単そうです。
しかし、大体において、実務で使うJSONはsample1.json
みたいに単純な構造はしていませんよね。
これから、 |@csv
を実戦で使う上での、3個の条件と8個のテクニックを紹介していきます。
3個の条件
|@csv
を有効に使うための条件を 3 つ紹介いたします。
条件(1) |@csv
の前段までにJSON(配列)の並びにする
出力(2)は、正確にはJSONになっていません。JSON(配列)の並びになっています。
> cat sample1.json | jq -r '.[] | [.key1, .key2, .key3]'
[
"value11",
"value12",
"value13"
]
[
"value21",
"value22",
"value23"
]
[
"value31",
"value32",
"value33"
]
[
"value41",
"value42",
"value43"
]
要素数3の配列が4つ並んでいます。各配列がCSVの行、配列中の各要素がCSVの列に相当します。|@csv
の前段までに、この形にどうやって導くかが重要になります。
条件(2) JSON(配列)の並びの、各配列の要素数は同じにする
JSON(配列)の並びの、各配列は同じ要素数、かつ、同じ意味を持つ要素の並びであることが望ましいです。
CSV形式のデータは、各行の要素数や並びに意味があるからです。
条件(3) JSON(配列)の並びの、各配列の要素は配列とオブジェクト以外にする
JSON は型として
- 数値
- 文字列
- 真偽値
- 配列
- オブジェクト
null
を持つことができます。このうちCSV形式で表示可能なのは、数値、文字列、真偽値、nullのみです。配列とオブジェクトは使えません。
|@csv
の前段までに、コマンドの標準入力に渡された JSON を、上記の条件(1) ~ (3) を満たす形式に変換します。
8個のテクニック
それでは、紹介した3個の条件を満たすためのテクニックを紹介します。
この章で使うサンプル(sample2.json
)は以下の通りです。
> cat sample2.json
[
{
"id" : 1,
"created_at" : "2018-05-27",
"attrs" : [
{ "key": "firstname", "value": "John" },
{ "key": "lastname", "value": "Smith" },
{ "key": "middlename", "value": "W" }
]
},
{
"id" : 2,
"created_at" : "2018-05-26",
"attrs" : [
{ "key": "firstname", "value": "太郎" },
{ "key": "lastname", "value": "山田" }
]
}
]
テクニック(1) 整形
> cat sample2.json | jq -r '.'
[
{
"id": 1,
"created_at": "2018-05-27",
"attrs": [
{
"key": "firstname",
"value": "John"
},
{
"key": "lastname",
"value": "Smith"
},
{
"key": "middlename",
"value": "W"
}
]
},
{
"id": 2,
"created_at": "2018-05-26",
"attrs": [
{
"key": "firstname",
"value": "太郎"
},
{
"key": "lastname",
"value": "山田"
}
]
}
]
'.'
で入力されたJSONを整形して出力してくれます。一番シンプルな使い方です。テクニックと言うほどの物ではないですが、基本中の基本なので挙げさせてもらいました。
テクニック(2) 皮むき
> > cat sample2.json | jq -r '.[]'
{
"id": 1,
"created_at": "2018-05-27",
"attrs": [
{
"key": "firstname",
"value": "John"
},
{
"key": "lastname",
"value": "Smith"
},
{
"key": "middlename",
"value": "W"
}
]
}
{
"id": 2,
"created_at": "2018-05-26",
"attrs": [
{
"key": "firstname",
"value": "太郎"
},
{
"key": "lastname",
"value": "山田"
}
]
}
sample2.json
に記載されているJSONの最上位が配列の場合、 .[]
で配列の要素のみを取り出すことが出来ます。私はこれを配列の皮むきとよんでいます。
テクニック(3) メンバーの値の取り出し
> cat sample2.json | jq -r '.[].id'
1
2
.<メンバー名>
で、オブジェクトから特定のメンバーの値を取り出せます。今回の場合は .[]
で皮むきして取り出した、配列の各要素から id
の値を取り出しています。
テクニック(4) 複数のメンバーの値の取り出し
> cat sample2.json | jq -r '.[].id, .[].created_at'
1
2
2018-05-27
2018-05-26
テクニック(3) で紹介したフィルターを ,
で連結することにより、複数のメンバーの値を取り出す事が出来ます。
今回の場合は、 .[]
で皮むきして取り出した、各要素から .id
と .created_at
の値を取り出しています。
テクニック(5) 共通部分のくくりだし
> cat sample2.json | jq -r '.[] | .id, .created_at'
1
2018-05-27
2
2018-05-26
,
で連結されたフィルターにおいて、共通部分があった場合、 |
を使ってくくり出す事が出来ます。
今回の場合は、 .[]
が共通部分なので、 |
の前にくくりだし、残りを |
の後に残します。
出力の並びもテクニック(4)の時と変わっていますね。この方が期待するJSON(配列)の並びの形に近づいています。
テクニック(6) 配列化
> cat sample2.json | jq -r '.[] | [.id, .created_at]'
[
1,
"2018-05-27"
]
[
2,
"2018-05-26"
]
[]
という記述は、テクニック(2)で紹介した配列の皮むき以外に、配列化に使えます。
今回の場合は、 .id
と .create_at
を [
と ]
で囲む事により、 .id
を0番目、 .create_at
を1番目の要素に持つ配列にしています。
テクニック(7) 配列から特定の値を取り出す
> cat sample2.json \
| jq -r '.[].attrs[] | select(.key == "firstname").value'
John
太郎
今回の例(sample2.json
)において、「attrs
メンバーからkey
が firstname
であるオブジェクトの value
の値を取り出したい」といった場合はどうすればいいでしょうか。
attr
メンバーの値の例
[
{ "key": "firstname", "value": "John" },
{ "key": "lastname", "value": "Smith" },
{ "key": "middlename", "value": "W" }
]
ここで使えるのが select
という jq
コマンドに組み込まれている機能です。
select(<取り出したいオブジェクトの条件>).<値を取り出したいメンバー名>
今回の場合、 select(.key == "firstname")
で {"key": "firstname", "value": "John"}
のみを対象とし、続く .value
で John
を取り出しています。
ここまでのテクニックで、 id
を0番目の要素、名前(firstname
)を1番目の要素に持つ配列を作成することが出来ます。
> cat sample2.json \
| jq -r '.[]
| [
.id,
(.attrs[] | select(.key == "firstname").value)
]'
[
1,
"John"
]
[
2,
"太郎"
]
テクニック(8) 条件を満たす要素がなかったときにnullで埋める
実は一番紹介したかったのがこのテクニックです。
CSVでは、1行に並べる要素の個数と順番が重要です。
ですが、 |@csv
の前段までに、配列の要素数が異なる場合が発生することがあります。
例えば、テクニック(7)の例において、ミドルネーム(middlename
)を対象としたい場合はどうなるでしょか。
うまくいかないやり方
> cat sample2.json \
| jq -r '.[]
| [ .id,
(.attrs[] | select(.key == "middlename").value)
]'
[
1,
"W"
]
[
2
]
一つ目の配列と、二つ目の配列の要素数が異なります。条件(2)を満たせていません。
この問題に対応するためには、新たに
- テクニック(8–1)
length
による配列の長さを計測 - テクニック(8–2)
if ~ then else ~ end
による制御構造
を導入します。
うまくいくやり方
> cat sample2.json \
| jq -r '.[]
| [ .id,
([(.attrs[] | select(.key == "middlename").value)]
| if length == 0 then
null
else
.[0]
end)
]'
[
1,
"W"
]
[
2,
null
]
複雑になってきましたが、もう少し頑張ってください。
うまくいかないやり方、で、
(.attrs[] | select(.key == "middlename").value)
としていたところを、うまくいくやり方で
([(.attrs[] | select(.key == "middlename").value)]
| if length == 0 then
null
else
.[0]
end)
に置き換えています。やっていることは以下の通りです。
- テクニック(6)を使って、値が存在しない可能性がある部分を
[]
でくくり、配列にする
([(.attrs[] | select(.key == "middlename").value)]
| if length == 0 then
null
else
.[0]
end)
- 配列の長さが 0 なら
null
を出力する
([(.attrs[] | select(.key == "middlename").value)]
| if length == 0 then
null
else
.[0]
end)
- 配列の長さが 0 ではないなら 配列の 0 番目の要素を出力する
([(.attrs[] | select(.key == "middlename").value)]
| if length == 0 then
null
else
.[0]
end)
この記述方法により、ミドルネームがない場合は null
を出力するようなりました。
さて、これまで紹介したテクニックを使って、
- 1列目: id
- 2列目:ミドルネーム
- 3列目:作成日
となる CSVを出力してみましょう。
> cat sample2.json \
| jq -r '.[]
| [ .id,
([(.attrs[] | select(.key == "middlename").value)]
| if length == 0 then
null
else
.[0]
end),
.created_at
]
|@csv'
1,"W","2018-05-27"
2,,"2018-05-26"
はい、出来ました!
引っ張ったわりにずいぶん地味な落ちになってしまいました。
チームのイマドキエンジニアの冷めた視線を感じる今日この頃ですが、次回も、めげずに jq
コマンドに関係した、短めな話題を扱いたいと思います。