LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@ukijumotahaneniarukenia

jqでJSON形式をTSV形式に変換

Last updated at Posted at 2019-11-15

まえがき

systemd-nspawnコンテナでコンテナのメタ情報等をJSON形式で取得できた。

保守しやすいようにJSON形式をTSV形式に変換できないかと考えた。

本文

仕様

配列にオブジェクトが複数件登録されているシンプルなJSONデータを標準入力として受け取り、ヘッダ行と明細行に分けて加工し、TSV形式で標準出力に出力する仕様

バージョン

$ jq --version
jq-1.6

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

インプット

$ cat test.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"
  },
  {
    "machine": "vir-ubuntu-18-04-002",
    "class": "container",
    "service": "systemd-nspawn",
    "os": "ubuntu",
    "version": "18.04",
    "addresses": "192.168.1.211\nfe80::18d7:6aff:fef7:799c"
  },
  {
    "machine": "vir-ubuntu-18-04-003",
    "class": "container",
    "service": "systemd-nspawn",
    "os": "ubuntu",
    "version": "18.04",
    "addresses": "192.168.1.212\nfe80::12:92ff:fe02:380e"
  },
  {
    "machine": "vir-ubuntu-18-04-004",
    "class": "container",
    "service": "systemd-nspawn",
    "os": "ubuntu",
    "version": "18.04",
    "addresses": "192.168.1.213\nfe80::c878:ecff:fe6b:f8cb"
  },
  {
    "machine": "vir-ubuntu-18-04-005",
    "class": "container",
    "service": "systemd-nspawn",
    "os": "ubuntu",
    "version": "18.04",
    "addresses": "192.168.1.214\nfe80::f0ad:efff:fec2:ddce"
  }
]

コマンド

ワンライナー

$ cat test.json | jq -r '. as $in | $in | map(keys)|reduce .[] as $item([];. + $item)|unique as $column_list | $in |  ($column_list|join("\t")),map(. as $entry |($column_list|map(. as $column|($entry[$column]|tojson))|join("\t")))[]'

ワンライナーじゃない

ユニークなカラム一覧を取得したあと、この一覧リストをヘッダ行の処理と明細行の処理に渡している。

明細行の処理でtojsonしているのは改行文字等がふくまれてくる可能性があるので、基本tojsonしておくといいと考えた。

cat test.json | \
    jq -r '. as $in |
           $in | map(keys)|reduce .[] as $item([];. + $item)|unique as $column_list |
           $in | 
           ($column_list|join("\t"))
           ,map(. as $entry |($column_list|map(. as $column|($entry[$column]|tojson))|join("\t")))[]'

アウトプット

addresses	class	machine	os	service	version
"192.168.1.210\nfe80::ec3e:dff:febc:4d55"	"container"	"vir-ubuntu-18-04-001"	"ubuntu"	"systemd-nspawn"	"18.04"
"192.168.1.211\nfe80::18d7:6aff:fef7:799c"	"container"	"vir-ubuntu-18-04-002"	"ubuntu"	"systemd-nspawn"	"18.04"
"192.168.1.212\nfe80::12:92ff:fe02:380e"	"container"	"vir-ubuntu-18-04-003"	"ubuntu"	"systemd-nspawn"	"18.04"
"192.168.1.213\nfe80::c878:ecff:fe6b:f8cb"	"container"	"vir-ubuntu-18-04-004"	"ubuntu"	"systemd-nspawn"	"18.04"
"192.168.1.214\nfe80::f0ad:efff:fec2:ddce"	"container"	"vir-ubuntu-18-04-005"	"ubuntu"	"systemd-nspawn"	"18.04"

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

インプット

$ 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"
  }
]

コマンド

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

アウトプット

addresses	class	machine	os	service	version
"192.168.1.210\nfe80::ec3e:dff:febc:4d55"	"container"	"vir-ubuntu-18-04-001"	"ubuntu"	"systemd-nspawn"	"18.04"

オブジェクト単一の場合

インプット

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

コマンド

ワンライナー

cat b.json | jq -r '. as $in | (if type == "array" then map(keys) else [keys] end) as $key_list | $key_list | reduce .[] as $item([];. + $item)|unique as $column_list | ($column_list|join("\t")),(if ($in|type) == "array" then ($in|map(. as $entry |($column_list|map(. as $column|($entry[$column]|tojson))|join("\t")))[]) else ($column_list|map(. as $column|($in[$column]|tojson))|join("\t")) end)'

ワンライナーじゃない

標準入力が配列の場合とオブジェクトの場合で処理を分岐するように上記２つのパタンのコマンドから少し改良した。

cat b.json | \
    jq -r '. as $in | 
        (if type == "array" then map(keys) else [keys] end) as $key_list | 
        $key_list | reduce .[] as $item([];. + $item)|unique as $column_list | 
        ($column_list|join("\t"))
        ,(
          if ($in|type) == "array" then ($in|map(. as $entry |($column_list|map(. as $column|($entry[$column]|tojson))|join("\t")))[]) 
          else ($column_list|map(. as $column|($in[$column]|tojson))|join("\t")) 
          end
        )'

アウトプット

ID	なまえ	役割
1	"ぽるこ"	"ぶた"

汎用コマンド化

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

# !/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 | (if type == "array" then map(keys) else [keys] end) as $key_list | $key_list | reduce .[] as $item([];. + $item)|unique as $column_list | ($column_list|join("\t")),(if ($in|type) == "array" then ($in|map(. as $entry |($column_list|map(. as $column|($entry[$column]|tojson))|join("\t")))[]) else ($column_list|map(. as $column|($in[$column]|tojson))|join("\t")) end)'

  else

    echo ${ARGS[0]} | \
      jq -r '. as $in | (if type == "array" then map(keys) else [keys] end) as $key_list | $key_list | reduce .[] as $item([];. + $item)|unique as $column_list | ($column_list|join("\t")),(if ($in|type) == "array" then ($in|map(. as $entry |($column_list|map(. as $column|($entry[$column]|tojson))|join("\t")))[]) else ($column_list|map(. as $column|($in[$column]|tojson))|join("\t")) end)'

  fi

}

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

使用感

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

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

   CMD: echo test.json | json2tbl-jq

   or

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


$ echo a.json  | json2tbl-jq
ID	なまえ	役割
1	"ぽるこ"	"ぶた"
2	"ジーナ"	"恋人"

$ echo '{"ID":1,"なまえ":"ぽるこ","役割":"ぶた"}' | json2tbl-jq
ID	なまえ	役割
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 | json2tbl-jq
addresses	class	machine	os	service	version
"192.168.1.210\nfe80::ec3e:dff:febc:4d55"	"container"	"vir-ubuntu-18-04-001"	"ubuntu"	"systemd-nspawn"	"18.04"

こういうのも便利

$ 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  '."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 '."Fruits"' | json2tbl-jq
Name	Price	Quantity
"Apple"	100	3
"Orange"	110	15
"Mango"	90	100
"Banana"	100	6
"Kiwifruit"	50	40

あとがき

jq便利

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?