こんにちは、さるまりんです。
JSON文字列を扱うことがよくありますが、それをそのままデータベースに格納できるんですね。
今回はそれをやってみました。
まずは、試すためのデータベースを用意します。
1. postgresユーザーで繋ぐ
psql -U postgres -h 127.0.0.1
2. salumarineユーザーを作成し、パスワードにsalupwに設定
create user salumarine with password 'salupw';
3. データベースsaludbを作成
create database saludb;
4. salumarineユーザーにsaludbへの権限付与
grant all privileges on database saludb to salumarine;
5. salumarineユーザーでsaludbに接続する
psql -U salumarine -h 127.0.0.1 saludb
↑を一気に実行したログです。
% psql -U postgres -h 127.0.0.1
Password for user postgres:
psql (14.2, server 13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.
postgres=# create user salumarine with password 'salupw';
CREATE ROLE
postgres=# create database saludb;
CREATE DATABASE
postgres=# grant all privileges on database saludb to salumarine;
GRANT
postgres=# \q
% psql -U salumarine -h 127.0.0.1 saludb
Password for user salumarine:
psql (14.2, server 13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.
saludb=>これで準備完了です。
何回もやるのですがなぜか忘れてしまうのでここにメモっておきます。
それでは本題です。
CREATE TABLE テーブル作成
JSON型のカラムを持つテーブルを作成してみます。カラムタイプはJSONです。
CREATE TABLE friends (id serial, json_data json);実行しテーブルができたか確認してみます。
saludb=> CREATE TABLE friends (id serial, json_data json);
CREATE TABLE
saludb=> \d friends
Table "public.friends"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('friends_id_seq'::regclass)
json_data | json | | |
saludb=>カラムjson_dataの型(Type)はjsonになっていますね。
INSERT 登録
ではデータを投入します。
このテーブルにJSON文字列をjson_dataに登録していきます。
INSERT INTO friends (json_data) VALUES
('{"name" : "バッハ", "country" : "ドイツ"}'),
('{"name" : "モーツァルト", "country" : "オーストリア"}'),
('{"name" : "ベートーヴェン", "country" : "ドイツ"}'),
('{"name" : "ブラームス", "country" : "ドイツ"}'),
('{"name" : "ドビュッシー", "country" : "フランス"}'),
('{"name" : "ホルスト", "country" : "イギリス"}'),
('{"name" : "シベリウス", "country" : "フィンランド"}'),
('{"name" : "ストラヴィンスキー", "country" : "ロシア"}');実行しました。
saludb=> INSERT INTO friends (json_data) VALUES
saludb-> ('{"name" : "バッハ", "country" : "ドイツ"}'),
saludb-> ('{"name" : "モーツァルト", "country" : "オーストリア"}'),
saludb-> ('{"name" : "ベートーヴェン", "country" : "ドイツ"}'),
saludb-> ('{"name" : "ブラームス", "country" : "ドイツ"}'),
saludb-> ('{"name" : "ドビュッシー", "country" : "フランス"}'),
saludb-> ('{"name" : "ホルスト", "country" : "イギリス"}'),
saludb-> ('{"name" : "シベリウス", "country" : "フィンランド"}'),
saludb-> ('{"name" : "ストラヴィンスキー", "country" : "ロシア"}');
INSERT 0 88レコードが登録されています。
SELECT 取得
SELECTして確認します。
saludb=> select * from friends;
id | json_data
----+-------------------------------------------------------
1 | {"name" : "バッハ", "country" : "ドイツ"}
2 | {"name" : "モーツァルト", "country" : "オーストリア"}
3 | {"name" : "ベートーヴェン", "country" : "ドイツ"}
4 | {"name" : "ブラームス", "country" : "ドイツ"}
5 | {"name" : "ドビュッシー", "country" : "フランス"}
6 | {"name" : "ホルスト", "country" : "イギリス"}
7 | {"name" : "シベリウス", "country" : "フィンランド"}
8 | {"name" : "ストラヴィンスキー", "country" : "ロシア"}
(8 rows)ちゃんと登録されていますね。
今度はJSON内の要素を特定して取得してみます。
それには->を利用します。
SELECT json_data -> 'name' AS name, json_data -> 'country' AS country
FROM friends;実行してみましょう。
saludb=> SELECT json_data -> 'name' AS name, json_data -> 'country' AS country
saludb-> FROM friends;
name | country
----------------------+----------------
"バッハ" | "ドイツ"
"モーツァルト" | "オーストリア"
"ベートーヴェン" | "ドイツ"
"ブラームス" | "ドイツ"
"ドビュッシー" | "フランス"
"ホルスト" | "イギリス"
"シベリウス" | "フィンランド"
"ストラヴィンスキー" | "ロシア"
(8 rows)nameとcountryそれぞれが取得できました。
今度は検索条件にしてみます。
検索条件では->>になっています。
SELECT json_data -> 'name' AS name, json_data -> 'country' AS country
FROM friends
WHERE json_data ->> 'country' = 'ドイツ';こちらも実行してみます。
saludb=> SELECT json_data -> 'name' AS name, json_data -> 'country' AS country
saludb-> FROM friends
saludb-> WHERE json_data ->> 'country' = 'ドイツ';
name | country
------------------+----------
"バッハ" | "ドイツ"
"ベートーヴェン" | "ドイツ"
"ブラームス" | "ドイツ"
(3 rows)“ドイツ”の友達だけ取得されました。
PostgreSQLにはJSON型のデータを扱うための関数やJSONのためのもうひとつのデータ型jsonbもあります。
これらを使うともっといろいろ便利になりそうなので今後勉強していきたいと思います。
読んでくださってありがとうございました。
それではまた！