jq コマンドで JSON を CSV に変換する

Goro Yanagi
VELTRA Engineering
Published in
13 min readJun 11, 2018

jq command supports conversion from JSON to CSV

JSON全盛のWeb界隈。しかし、JSONの特定の値を抜き出してExcelにまとめたい事がありますよね。今日はそんな願いを1 linerで実現する方法を紹介します。

環境

基本的な方針

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 メンバーからkeyfirstname であるオブジェクトの value の値を取り出したい」といった場合はどうすればいいでしょうか。

attr メンバーの値の例

[
{ "key": "firstname", "value": "John" },
{ "key": "lastname", "value": "Smith" },
{ "key": "middlename", "value": "W" }
]

ここで使えるのが select という jq コマンドに組み込まれている機能です。

select(<取り出したいオブジェクトの条件>).<値を取り出したいメンバー名>

今回の場合、 select(.key == "firstname"){"key": "firstname", "value": "John"} のみを対象とし、続く .valueJohn を取り出しています。

ここまでのテクニックで、 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 コマンドに関係した、短めな話題を扱いたいと思います。

--

--