jqでjsonデータをキーバリュー形式に変換

# まえがき

前書いた記事はこういうことをやりたかったんだなと踏まえて、おもむろに書き直しました。

# 本文


## 仕様

標準入力にJSON形式データを読み込み、TSV形式のキーバリューデータ構造に変換して標準出力に出力するコマンド


## バージョン

```
$ jq --version
jq-1.6
```

## 配列にオブジェクトが複数件が格納されている場合

### インプット

```
$ cat a.json 
[
  {
    "ID": 1,
    "なまえ": "ぽるこ",
    "役割": "ぶた"
  },
  {
    "ID": 2,
    "なまえ": "ジーナ",
    "役割": "恋人"
  }
]
```

### コマンド

ワンライナー

```
$ cat a.json | jq -r '. as $in|[path(..)] | map(. as $path | {"k":((if ($in|type) == "array" then "." else "" end)+($path|map((if type == "string" then "." + tojson else "["+tostring+"]" end))|join(""))),"v":($in|getpath($path))})[]|"\(.k)\t\(.v|tojson)"' | sed '1ikey\tvalue'
```

ワンライナーじゃない

パス式を以下の部分で列挙する

```
$ cat a.json | jq -rc '. as $in|[path(..)]'
[[],[0],[0,"ID"],[0,"なまえ"],[0,"役割"],[1],[1,"ID"],[1,"なまえ"],[1,"役割"]]
```

列挙したパス式をgetpath関数の引数に渡すため整形する

整形ロジックは文字列の場合はtojsonでエスケープ込みの文字列化して、数値の場合は文字列化して配列インデックス式を作成する

標準入力が配列の場合は前にドットを付与するようにしている

```
$ cat a.json | \
    jq -r '. as $in|[path(..)] |
           map(. as $path | {
                              "k" : ((if ($in|type) == "array" then "." else "" end) + ($path|map((if type == "string" then "." + tojson else "["+tostring+"]" end))|join("")))
                              ,"v":($in|getpath($path))
                            }
              )[] | 
           "\(.k)\t\(.v|tojson)"' | \
           sed '1ikey\tvalue'
```


### アウトプット

```
key	value
.	[{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"},{"ID":2,"なまえ":"ジーナ","役割":"恋人"}]
.[0]	{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"}
.[0]."ID"	1
.[0]."なまえ"	"ぽるこ"
.[0]."役割"	"ぶた"
.[1]	{"ID":2,"なまえ":"ジーナ","役割":"恋人"}
.[1]."ID"	2
.[1]."なまえ"	"ジーナ"
.[1]."役割"	"恋人"
```

## 配列にオブジェクトが単一件が格納されている場合

### インプット

```
$ cat c.json
[
  {
    "machine": "vir-ubuntu-18-04-001",
    "class": "container",
    "service": "systemd-nspawn",
    "os": "ubuntu",
    "version": "18.04",
    "addresses": "192.168.1.210\nfe80::ec3e:dff:febc:4d55"
  }
]
```

### コマンド

配列にオブジェクトが複数件が格納されている場合と同じ

### アウトプット

```
key	value
.	[{"machine":"vir-ubuntu-18-04-001","class":"container","service":"systemd-nspawn","os":"ubuntu","version":"18.04","addresses":"192.168.1.210\nfe80::ec3e:dff:febc:4d55"}]
.[0]	{"machine":"vir-ubuntu-18-04-001","class":"container","service":"systemd-nspawn","os":"ubuntu","version":"18.04","addresses":"192.168.1.210\nfe80::ec3e:dff:febc:4d55"}
.[0]."machine"	"vir-ubuntu-18-04-001"
.[0]."class"	"container"
.[0]."service"	"systemd-nspawn"
.[0]."os"	"ubuntu"
.[0]."version"	"18.04"
.[0]."addresses"	"192.168.1.210\nfe80::ec3e:dff:febc:4d55"
```

## オブジェクト単一の場合

### インプット

```
$ cat b.json
{
  "ID": 1,
  "なまえ": "ぽるこ",
  "役割": "ぶた"
}
```

### コマンド

配列にオブジェクトが複数件が格納されている場合と同じ

### アウトプット

```
key	value
	{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"}
."ID"	1
."なまえ"	"ぽるこ"
."役割"	"ぶた"
```

## 汎用コマンド化

以下の内容をjson2kvなどの名前でファイル保存して実行権限与えてパスを通せば、ハンディに扱える。

```
#!/usr/bin/env bash

usage(){
cat <<EOS
Usage:
   CMD: echo test.json | ${0##*/}
   or
   CMD: echo '{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"}' | ${0##*/}
EOS

exit 0

}

main(){
  ARGS=($(cat -));

  CNT=${#ARGS[@]}

  if [ -z $CNT ] ;then

    usage

  fi

  if [[ 1 -ne $CNT ]] ;then

    usage

  fi

  if [ -f ${ARGS[0]} ] ;then

    cat ${ARGS[0]} | \
      sed '/^$/d' | \
      jq -r '. as $in|[path(..)] | map(. as $path | {"k":((if ($in|type) == "array" then "." else "" end)+($path|map((if type == "string" then "." + tojson else "["+tostring+"]" end))|join(""))),"v":($in|getpath($path))})[]|"\(.k)\t\(.v|tojson)"' | sed '1ikey\tvalue'

  else

    echo ${ARGS[0]} | \
      jq -r '. as $in|[path(..)] | map(. as $path | {"k":((if ($in|type) == "array" then "." else "" end)+($path|map((if type == "string" then "." + tojson else "["+tostring+"]" end))|join(""))),"v":($in|getpath($path))})[]|"\(.k)\t\(.v|tojson)"' | sed '1ikey\tvalue'

  fi

}

[ -p /dev/stdin ] && cat - | main
[ -p /dev/stdin ] || echo -ne "$@" | main
```

使用感

```
$ cat a.json
[
  {
    "ID": 1,
    "なまえ": "ぽるこ",
    "役割": "ぶた"
  },
  {
    "ID": 2,
    "なまえ": "ジーナ",
    "役割": "恋人"
  }
]

#ファイル名を渡さないとエラー
$ cat a.json | json2kv-jq
Usage:

   CMD: echo test.json | json2kv-jq

   or

   CMD: echo '{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"}' | json2kv-jq

$ echo a.json  | json2kv-jq
key	value
.	[{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"},{"ID":2,"なまえ":"ジーナ","役割":"恋人"}]
.[0]	{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"}
.[0]."ID"	1
.[0]."なまえ"	"ぽるこ"
.[0]."役割"	"ぶた"
.[1]	{"ID":2,"なまえ":"ジーナ","役割":"恋人"}
.[1]."ID"	2
.[1]."なまえ"	"ジーナ"
.[1]."役割"	"恋人"

$ cat c.json
[
  {
    "machine": "vir-ubuntu-18-04-001",
    "class": "container",
    "service": "systemd-nspawn",
    "os": "ubuntu",
    "version": "18.04",
    "addresses": "192.168.1.210\nfe80::ec3e:dff:febc:4d55"
  }
]

$ echo c.json | json2kv-jq
key	value
.	[{"machine":"vir-ubuntu-18-04-001","class":"container","service":"systemd-nspawn","os":"ubuntu","version":"18.04","addresses":"192.168.1.210\nfe80::ec3e:dff:febc:4d55"}]
.[0]	{"machine":"vir-ubuntu-18-04-001","class":"container","service":"systemd-nspawn","os":"ubuntu","version":"18.04","addresses":"192.168.1.210\nfe80::ec3e:dff:febc:4d55"}
.[0]."machine"	"vir-ubuntu-18-04-001"
.[0]."class"	"container"
.[0]."service"	"systemd-nspawn"
.[0]."os"	"ubuntu"
.[0]."version"	"18.04"
.[0]."addresses"	"192.168.1.210\nfe80::ec3e:dff:febc:4d55"
```

こういうのも便利

```
$ cat sample.json 
{
  "Fruits": [
    {
      "Name": "Apple",
      "Quantity": 3,
      "Price": 100
    },
    {
      "Name": "Orange",
      "Quantity": 15,
      "Price": 110
    },
    {
      "Name": "Mango",
      "Quantity": 100,
      "Price": 90
    },
    {
      "Name": "Banana",
      "Quantity": 6,
      "Price": 100
    },
    {
      "Name": "Kiwifruit",
      "Quantity": 40,
      "Price": 50
    }
  ]
}

$ cat sample.json | jq -c ''
{"Fruits":[{"Name":"Apple","Quantity":3,"Price":100},{"Name":"Orange","Quantity":15,"Price":110},{"Name":"Mango","Quantity":100,"Price":90},{"Name":"Banana","Quantity":6,"Price":100},{"Name":"Kiwifruit","Quantity":40,"Price":50}]}

$ cat sample.json | jq -c '' | json2kv-jq
key	value
	{"Fruits":[{"Name":"Apple","Quantity":3,"Price":100},{"Name":"Orange","Quantity":15,"Price":110},{"Name":"Mango","Quantity":100,"Price":90},{"Name":"Banana","Quantity":6,"Price":100},{"Name":"Kiwifruit","Quantity":40,"Price":50}]}
."Fruits"	[{"Name":"Apple","Quantity":3,"Price":100},{"Name":"Orange","Quantity":15,"Price":110},{"Name":"Mango","Quantity":100,"Price":90},{"Name":"Banana","Quantity":6,"Price":100},{"Name":"Kiwifruit","Quantity":40,"Price":50}]
."Fruits"[0]	{"Name":"Apple","Quantity":3,"Price":100}
."Fruits"[0]."Name"	"Apple"
."Fruits"[0]."Quantity"	3
."Fruits"[0]."Price"	100
."Fruits"[1]	{"Name":"Orange","Quantity":15,"Price":110}
."Fruits"[1]."Name"	"Orange"
."Fruits"[1]."Quantity"	15
."Fruits"[1]."Price"	110
."Fruits"[2]	{"Name":"Mango","Quantity":100,"Price":90}
."Fruits"[2]."Name"	"Mango"
."Fruits"[2]."Quantity"	100
."Fruits"[2]."Price"	90
."Fruits"[3]	{"Name":"Banana","Quantity":6,"Price":100}
."Fruits"[3]."Name"	"Banana"
."Fruits"[3]."Quantity"	6
."Fruits"[3]."Price"	100
."Fruits"[4]	{"Name":"Kiwifruit","Quantity":40,"Price":50}
."Fruits"[4]."Name"	"Kiwifruit"
."Fruits"[4]."Quantity"	40
."Fruits"[4]."Price"	50

#keyのパス式はそのまま使える
$ cat sample.json | jq '."Fruits"[4]."Price"'
50

$ cat sample.json | jq '."Fruits"[4]."Name"'
"Kiwifruit"
```


# あとがき

jq便利