JSONファイルからDBのテーブル形式のデータを抽出する

階層構造になっているJSONファイルから、DBに登録するためのテーブル形式のデータを抽出する方法を考えます。例として、伝票データをJSONファイルで受け取って、DBの伝票テーブルと伝票明細テーブルに登録するためのデータを作成してみます。

TL;DR

• JSONデータのフラット化にはpandas.json_normalize()を使います。
• 階層構造の親子データを紐づけるために、pandas.json_normalize(record_path="children", meta=["parent_id"])を使います。
• 入力データに存在しない列を追加するときにはpandas.DataFrame.reindex()が便利です。

入出力データ

入力となるJSONデータのイメージがこちら。

slip.json

[
    {
        "slip_no": 1234,
        "amount": 2400,
        "destination": {
            "cd": "A0001",
            "name": "ほげほげマート"
        },
        "details": [
            {
                "item_cd": "item0001",
                "item_name": "商品A",
                "price":800,
                "count": 3
            }
        ]
    },
    {
        "slip_no": 5678,
        "amount": 1300,
        "destination": {
            "cd": "A1002",
            "name": "ふがふがストア"
        },
        "details": [
            {
                "item_cd": "item0001",
                "item_name": "商品A",
                "price":800,
                "count": 2
            },
            {
                "item_cd": "item0002",
                "item_name": "商品B",
                "price":100,
                "count": 5
            }
        ]
    },
    {
        "slip_no": 1122,
        "amount": 3100,
        "destination": {
            "cd": "A3001",
            "name": "ピヨピヨ商店"
        },
        "details": [
            {
                "item_cd": "item0001",
                "item_name": "商品A",
                "price":800,
                "count": 2
            },
            {
                "item_cd": "item0002",
                "item_name": "商品B",
                "price":100,
                "count": 5
            },
            {
                "item_cd": "item0003",
                "item_name": "商品C",
                "price":50,
                "count": 10
            },
            {
                "item_cd": "item0004",
                "item_name": "商品D",
                "price":500,
                "count": 1
            }
        ]
    }
]

出力となるテーブル形式のイメージがこちらです。

slip_no	amount	details	destination.cd	destination.name
1234	2400	"[{'item_cd': 'item0001', 'item_name': '商品A', 'price': 800, 'count': 3}]"	A0001	ほげほげマート
5678	1300	"[{'item_cd': 'item0001', 'item_name': '商品A', 'price': 800, 'count': 2}, {'item_cd': 'item0002', 'item_name': '商品B', 'price': 100, 'count': 5}]"	A1002	ふがふがストア
1122	3100	"[{'item_cd': 'item0001', 'item_name': '商品A', 'price': 800, 'count': 2}, {'item_cd': 'item0002', 'item_name': '商品B', 'price': 100, 'count': 5}, {'item_cd': 'item0003', 'item_name': '商品C', 'price': 50, 'count': 10}, {'item_cd': 'item0004', 'item_name': '商品D', 'price': 500, 'count': 1}]"	A3001	ピヨピヨ商店

slip_no	item_cd	item_name	price	count	updated_by	updated_date
1234	item0001	商品A	800	3	patch_230225	2023-02-25 18:00:00
5678	item0001	商品A	800	2	patch_230225	2023-02-25 18:00:00
5678	item0002	商品B	100	5	patch_230225	2023-02-25 18:00:00
1122	item0001	商品A	800	2	patch_230225	2023-02-25 18:00:00
1122	item0002	商品B	100	5	patch_230225	2023-02-25 18:00:00
1122	item0003	商品C	50	10	patch_230225	2023-02-25 18:00:00
1122	item0004	商品D	500	1	patch_230225	2023-02-25 18:00:00

データ抽出のポイントとしては、下記の3点です。

• オブジェクトとして定義されている項目のフラット化
• 親子データを対応付けるためのキー項目付与
• 入力データにない列の設定

オブジェクトとして定義されている項目のフラット化

オブジェクトとして定義されている項目（例ではdestination）をフラット化したいです。こんな時にはpandas.json_normalize()を使います。

normalize.py

import pands as pa
import json
import sys

file_name = sys.argv[1]
df = pa.read_json(file_name)

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(df)

print(slip_head)

実行結果は以下のようになります。destination.cdのように、オブジェクト名.メンバー名の形式でフラット化されているのがわかります。

$ python3 normalize.py ./slip.json
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店

ただし、これだけでは配列型がそのまま出力されていますので、次は配列型のデータを抽出してみます。

親子データを対応付けるためのキー項目付与

配列型のデータを抽出するためには、pandas.json_normalize()のrecord_pathという引数に項目名を指定します。

normalize.py

import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail )

$ python3 normalize.py ./slip.json 
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店
----------------------------------------
    item_cd item_name  price  count slip_no
0  item0001       商品A    800      3    1234
1  item0001       商品A    800      2    5678
2  item0002       商品B    100      5    5678
3  item0001       商品A    800      2    1122
4  item0002       商品B    100      5    1122
5  item0003       商品C     50     10    1122
6  item0004       商品D    500      1    1122

これだけでは親子関係を紐づけるための項目（伝票番号）が足りません。pandas.json_normalize()では、metaという引数を使って親の項目を付与して抽出することができます。

伝票番号（slip_no）を親のキーとして付与する例を示します。

normalize.py

import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail )

最後の列にslip_noという列が追加されて抽出されます。

$ python3 normalize.py ./slip.json
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店
----------------------------------------
    item_cd item_name  price  count slip_no
0  item0001       商品A    800      3    1234
1  item0001       商品A    800      2    5678
2  item0002       商品B    100      5    5678
3  item0001       商品A    800      2    1122
4  item0002       商品B    100      5    1122
5  item0003       商品C     50     10    1122
6  item0004       商品D    500      1    1122

また、データの構造によっては親と子に同じ項目名が含まれていることもあるでしょう。その場合にはpandas.json_normalize()のmeta_prefixという引数を使ってmetaで指定した項目にプレフィックスを付与することもできます。

入力データにない列の設定

DBのテーブルに追加したいとき、元のデータに存在しない列を追加したくなる場合があります。例えば、updated_byやupdated_dateなどのシステム管理の列です。

この場合にはpandas.DataFrame.reindex()が使えます。この関数の主な役割は、index（DataFrameを2次元配列ととらえた場合の行）を編集することですが、同時に列も編集することができます。columnsという引数に、新しく生成されるDataFrameで利用したい列を指定することで任意の列を追加することができます。

また、columnsに指定する順番で、列の順番を入れ替えることもできます。

normalize.py

import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail)

print('----------------------------------------')

# 列を追加する。
slip_detail = slip_detail.reindex(columns=['slip_no', 'item_cd', 'item_name', 'price', 'count', 'updated_by', 'updated_date'])
print(slip_detail)

$ python3 normalize.py ./slip.json
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店
----------------------------------------
    item_cd item_name  price  count slip_no
0  item0001       商品A    800      3    1234
1  item0001       商品A    800      2    5678
2  item0002       商品B    100      5    5678
3  item0001       商品A    800      2    1122
4  item0002       商品B    100      5    1122
5  item0003       商品C     50     10    1122
6  item0004       商品D    500      1    1122
----------------------------------------
  slip_no   item_cd item_name  price  count  updated_by  updated_date
0    1234  item0001       商品A    800      3         NaN           NaN
1    5678  item0001       商品A    800      2         NaN           NaN
2    5678  item0002       商品B    100      5         NaN           NaN
3    1122  item0001       商品A    800      2         NaN           NaN
4    1122  item0002       商品B    100      5         NaN           NaN
5    1122  item0003       商品C     50     10         NaN           NaN
6    1122  item0004       商品D    500      1         NaN           NaN

CSVファイルに出力

最後に、伝票ヘッダと伝票明細をCSVファイルに出力します。index=Falseをつけないと、DataFrameのindexが出力されてしまってテーブルの形になりませんので注意してください。

normalize.py

import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail)

print('----------------------------------------')

# 列を追加する。
slip_detail = slip_detail.reindex(columns=['slip_no', 'item_cd', 'item_name', 'price', 'count', 'updated_by', 'updated_date'])
print(slip_detail)

# CSVファイルに出力する。
slip_head.to_csv('slip_head', index=False)
slip_detail.to_csv('slip_detail', index=False)

まとめ

階層構造を持つJSONファイルから、pandas.json_normalize()とpandas.DataFrame.reindex()を使ってDBのテーブル形式でデータを取り出す方法を見てきました。最終的には、ここで抽出したデータをExcelか何かに貼り付けて、追加した列に固定値を埋めるなりしてDBに投入すれば良いと思います。ここまでやったのだからDBへの投入もPythonでやればいいと思うかもしれませんが、本番環境に投入するデータは投入する前にレビューしたいじゃないですか（SIer脳）。

また、記事を書きながら「どうせなら明細番号とかも付与したい」と思いましたが、多分pandas.DataFrame.groupby.cumcount()でできます。

slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
slip_detail['detail_no'] = slip_detail.groupby('slip_no').cumcount()

参考資料

• pandas.json_normalize
• pandas.DataFrame.reindex
• pandas.DataFrame.cumcount
• pandasのjson_normalizeで辞書のリストをDataFrameに変換
• Pandasのjson_normalizeを詳しく調べてみる
• Pandasで同一のIDだった時に連番を追加する