【PostgreSQL】XMLとJSON型の操作について

はじめに
XML
JSON

はじめに

オープンソースのRDBMSである「PostgreSQL」を使って、
XML型とJSON型の抽出と検索の方法です。

バージョンはwindows版の13.3を入れました。

XML

XML型が入るテーブルを準備します。

CREATE TABLE straw_hat_pirates_xml(
 id integer,
 val xml --xml型で作成
);

以下の方法でXML型にデータを挿入できます。

INSERT INTO straw_hat_pirates_xml
VALUES
 (1,'<member><name>ルフィ</name><position>船長</position><bounty>1500000000</bounty></member>')
 ,(2,'<member><name>ゾロ</name><position>剣士</position><bounty>320000000</bounty></member>')
 ,(3,'<member><name>ナミ</name><position>航海士</position><bounty>66000000</bounty></member>')
 ,(4,'<member><name>ウソップ</name><position>狙撃手</position><bounty>200000000</bounty></member>')
 ,(5,'<member><name>サンジ</name><position>コック</position><bounty>330000000</bounty></member>')
 ,(6,'<member><name>チョッパー</name><position>船医</position><bounty>100</bounty></member>')
 ,(7,'<member><name>ロビン</name><position>考古学者</position><bounty>130000000</bounty></member>')
 ,(8,'<member><name>フランキー</name><position>船大工</position><bounty>94000000</bounty></member>')
 ,(9,'<member><name>ブルック</name><position>音楽家</position><bounty>83000000</bounty></member>')
 ,(10,'<member><name>ジンベエ</name><position>操舵手</position><bounty>438000000</bounty></member>');

SELECTする際はXPATHで抽出できます。
TEXTにキャストすることで文字列として取得できます。

SELECT
 XPATH('/member/name/text()', val),
 XPATH('/member/name/text()', val)::TEXT,
 (XPATH('/member/name/text()', val)::TEXT[])[1]
FROM
 straw_hat_pirates_xml;

WHERE句にもXPATHが使用できます。
今回はINT型にキャストして1億ベリー以上のメンバー抽出しました。

SELECT
 (XPATH('/member/name/text()',val)::TEXT[])[1]
 ,(XPATH('/member/position/text()',val)::TEXT[])[1]
 ,(XPATH('/member/bounty/text()',val)::TEXT[])[1]::INT
FROM straw_hat_pirates_xml
 WHERE (XPATH('/member/bounty/text()',val)::TEXT[])[1]::INT >= 100000000;

JSON

JSON型が入るテーブルを準備します。

CREATE TABLE straw_hat_pirates_json(
 id integer,
 val json --json 型で作成
);

以下の方法でJSON型にデータを挿入できます。

INSERT INTO straw_hat_pirates_json
VALUES
 (1,'{"member":{"name":"ルフィ","position":"船長","bounty":"1500000000"}}')
 ,(2,'{"member":{"name":"ゾロ","position":"剣士","bounty":"320000000"}}')
 ,(3,'{"member":{"name":"ナミ","position":"航海士","bounty":"66000000"}}')
 ,(4,'{"member":{"name":"ウソップ","position":"狙撃手","bounty":"200000000"}}')
 ,(5,'{"member":{"name":"サンジ","position":"コック","bounty":"330000000"}}')
 ,(6,'{"member":{"name":"チョッパー","position":"船医","bounty":"100"}}')
 ,(7,'{"member":{"name":"ロビン","position":"考古学者","bounty":"130000000"}}')
 ,(8,'{"member":{"name":"フランキー","position":"船大工","bounty":"94000000"}}')
 ,(9,'{"member":{"name":"ブルック","position":"音楽家","bounty":"83000000"}}')
 ,(10,'{"member":{"name":"ジンベエ","position":"操舵手","bounty":"438000000"}}');

SELECTする際は「->」で取得可能です。
「->」ではJSONのまま抽出して、「->>」でtextとして取得できます。

SELECT
 val->'member'
 ,val->'member'->'name'
 ,val->'member'->>'name'
FROM
 straw_hat_pirates_json;

WHERE句にも以下の方法で使用できます。
こちらもINT型にキャストして1億ベリー以上のメンバーを抽出しました。

SELECT
 val->'member'->>'name'
 ,val->'member'->>'position'
 ,(val->'member'->>'bounty')::INT
FROM
 straw_hat_pirates_json
WHERE
 (val->'member'->>'bounty')::INT>= 100000000;