【PostgreSQL】XMLとJSON型の操作について
はじめに
オープンソースのRDBMSである「PostgreSQL」を使って、
XML型とJSON型の抽出と検索の方法です。
バージョンはwindows版の13.3を入れました。
「PostgreSQL」のインストール方法は以下をご参照ください!
dohidoi.hatenablog.com
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;
XML型とJSON型の抽出と検索の方法について以上です!
今後もPostgreSQLについて書けたらと思います~