Postgresを利用してJSONを操作する。第二段

環境	バージョン
DB	PostgreSQL 15.2

テーブル準備

CREATE TABLE users (
    id SERIAL,
    name VARCHAR(256),
    profile JSON,
    tags JSON,
    PRIMARY KEY(id)
);

INSERT INTO users(name, profile, tags) VALUES('test 1', '{"pref": "東京都","age": 30}', '[{"isNew": true}, {"area": "関東"}, {"discount": "セール流入"}, {"care": "電話"}, {"attribute": [{"hoby": "検索"}]}]');
INSERT INTO users(name, profile, tags) VALUES('test 2', '{"pref": "大阪府","age": 25}', '[{"isNew": true}, {"area": "関西"}, {"direct": "営業流入"}, {"inquiry_time": "夜間のみ"}, {"attribute": [{"hoby": "ショッピング"}]}]');
INSERT INTO users(name, profile, tags) VALUES('test 3', '{"pref": "神奈川県","age": 30}', '[{"isNew" : false}, {"area": "関東"}, {"care": "メール"}, {"attribute": [{"hoby": "動画鑑賞"}]}]');

サンプル一覧

自分が便利と思えるSQLを記載。

json_array_length： JSON配列の長さを返す

json_array_lengthでは配列の長さを返します。注意点としては、profileのようなキーを持つ項目には利用できない点です。

SELECT json_array_length(tags) FROM users;
 json_array_length 
-------------------
                 5
                 5
                 4

以下のようにエラーとなるため利用不可です。

SELECT json_array_length(profile) FROM users;
ERROR:  cannot get array length of a non-array

json_each: JSONオブジェクトをkey/value組合せの集合を返す

実行結果は、配列にして返却されます。こちら単体であまり使う感じはないですが、key valueを切り分けたいときは便利です。

SELECT profile, key, value FROM users, json_each(users.profile);
            profile             | key  |   value    
--------------------------------+------+------------
 {"pref": "東京都","age": 30}   | pref | "東京都"
 {"pref": "東京都","age": 30}   | age  | 30
 {"pref": "大阪府","age": 25}   | pref | "大阪府"
 {"pref": "大阪府","age": 25}   | age  | 25
 {"pref": "神奈川県","age": 30} | pref | "神奈川県"
 {"pref": "神奈川県","age": 30} | age  | 30
(6 rows)

戻り値をtext型に固定したい場合はjson_each_textを利用します。

SELECT profile, key, value FROM users, json_each_text(users.profile);
            profile             | key  |  value   
--------------------------------+------+----------
 {"pref": "東京都","age": 30}   | pref | 東京都
 {"pref": "東京都","age": 30}   | age  | 30
 {"pref": "大阪府","age": 25}   | pref | 大阪府
 {"pref": "大阪府","age": 25}   | age  | 25
 {"pref": "神奈川県","age": 30} | pref | 神奈川県
 {"pref": "神奈川県","age": 30} | age  | 30
(6 rows)

今度は、通常配列では利用できないので注意してください。

SELECT tags, key, value FROM users, json_each(users.tags);
ERROR:  cannot deconstruct an array as an object

json_extract_path: JSONオブジェクト内で引数を用いて検索する

演算子#>と同じ役割なのでこの関数はそこまで利用しなくても良いかなとは思います。こんなのがあるのか程度の内容です。

SELECT json_extract_path(tags, '0', 'isNew'), json_extract_path(tags, '2') FROM users;
 json_extract_path |     json_extract_path      
-------------------+----------------------------
 true              | {"discount": "セール流入"}
 true              | {"direct": "営業流入"}
 false             | {"care": "メール"}
(3 rows)

json_object_keys: JSONオブジェクトの中のキー一式を返す。

オブジェクトのキーを返却する関数です。配列で複数個あると、個数分返すため実際のSQLを組むときは工夫が必要です。

SELECT profile, json_object_keys(profile) FROM users;
            profile             | json_object_keys 
--------------------------------+------------------
 {"pref": "東京都","age": 30}   | pref
 {"pref": "東京都","age": 30}   | age
 {"pref": "大阪府","age": 25}   | pref
 {"pref": "大阪府","age": 25}   | age
 {"pref": "神奈川県","age": 30} | pref
 {"pref": "神奈川県","age": 30} | age
(6 rows)

通常配列で検索すると失敗するので留意必要です。

SELECT json_object_keys(tags) FROM users;
ERROR:  cannot call json_object_keys on an array

json_array_elements: JSON配列をJSON値の集合に展開する。

JSON配列の中身を展開する。シンプルですがNGな使い方も紹介

SELECT json_array_elements(tags) FROM users;
            json_array_elements            
-------------------------------------------
 {"isNew": true}
 {"area": "関東"}
 {"discount": "セール流入"}
 {"care": "電話"}
 {"attribute": [{"hoby": "検索"}]}
 {"isNew": true}
 {"area": "関西"}
 {"direct": "営業流入"}
 {"inquiry_time": "夜間のみ"}
 {"attribute": [{"hoby": "ショッピング"}]}
 {"isNew" : false}
 {"area": "関東"}
 {"care": "メール"}
 {"attribute": [{"hoby": "動画鑑賞"}]}
(14 rows)

NGケース１、nameのカラムとセットで利用する場合に返却結果が配列のためNGとなります。

SELECT name, json_array_elements(profile) FROM users;
ERROR:  cannot call json_array_elements on a non-array

json_array_elements_text: JSON配列をtext値の集合に展開する。

先ほどのjson_array_elementsとは異なり、nameの指定も可能なので利便性はこちらの方があるかと思いいます。

SELECT name, json_array_elements_text(tags) FROM users;
  name  |         json_array_elements_text          
--------+-------------------------------------------
 test 1 | {"isNew": true}
 test 1 | {"area": "関東"}
 test 1 | {"discount": "セール流入"}
 test 1 | {"care": "電話"}
 test 1 | {"attribute": [{"hoby": "検索"}]}
 test 2 | {"isNew": true}
 test 2 | {"area": "関西"}
 test 2 | {"direct": "営業流入"}
 test 2 | {"inquiry_time": "夜間のみ"}
 test 2 | {"attribute": [{"hoby": "ショッピング"}]}
 test 3 | {"isNew" : false}
 test 3 | {"area": "関東"}
 test 3 | {"care": "メール"}
 test 3 | {"attribute": [{"hoby": "動画鑑賞"}]}
(14 rows)

json_typeof: JSON値の型をテキスト文字列として返す。

格納されている型を返却します。オブジェクトレベルで指定しないといけないので参照させたりして利用する方が多いと思います。

SELECT name, json_typeof(profile), json_typeof(profile->'age'), json_typeof(tags) FROM users;
  name  | json_typeof | json_typeof | json_typeof 
--------+-------------+-------------+-------------
 test 1 | object      | number      | array
 test 2 | object      | number      | array
 test 3 | object      | number      | array
(3 rows)

json_to_record: JSONオブジェクトから任意のレコードを作成します

カラム毎に分割したい場合に役に立つ関数です。データが大量にあったり、する場合にカラムのみで利用できるため活用性高いです。

SELECT pref, age, dummy FROM users, json_to_record(users.profile) AS x(pref text, age int, dummy text);
   pref   | age | dummy 
----------+-----+-------
 東京都   |  30 | 
 大阪府   |  25 | 
 神奈川県 |  30 | 
(3 rows)

まとめ

色々なJSON関数をまとめましたが色々な方法が取れるのだと思いました。サンプルとして作ったので自分の利用時にはもっと複雑になっています。