はじまる

適当な事を適当に書く

Postgres あるテーブルからデータをJSON 型で抽出するときの小ネタ

情報処理 Python PostgreSQL

前置き

たまにテーブルのレコードの任意の列をJSONとして抽出したいときがあるので、そのメモ。

前提

こんな 動物 テーブルがあるとします。

SELECT * FROM 動物;
id name age kind
1 Mickey 5 Mouse
2 Snoopy 3 Dog
3 Kitty 3 Cat

テーブルの全ての列をJSONにして抽出する場合

この場合、to_json を利用します。引数はテーブルの名称です。

SELECT to_json(動物) FROM 動物;
to_json
{"id":1,"name":"Mickey","age":5,"kind":"Mouse"}
{"id":2,"name":"Snoopy","age":3,"kind":"Dog"}
{"id":3,"name":"Kitty","age":3,"kind":"Cat"}

抽出する列を絞りたい場合、to_json の引数で列名を指定することはできない為、WITH 句やサブクエリを使いましょう。

WITH 動物2 AS (SELECT id, name FROM 動物)
SELECT to_json(動物2) FROM 動物2;
to_json
{"id":1,"name":"Mickey"}
{"id":2,"name":"Snoopy"}
{"id":3,"name":"Kitty"}

テーブルの一部の列をJSON、一部の列は別の型で同時に抽出する場合

row_to_json を使いましょう。

SELECT
    id,
    (
        select  row_to_json(_)
        from (select name, age, kind) as _
    )
FROM 動物;
id row_to_json
1 {"name":"Mickey","age":5,"kind":"Mouse"}
2 {"name":"Snoopy","age":3,"kind":"Dog"}
3 {"name":"Kitty","age":3,"kind":"Cat"}

参考