PostgreSQLのJSON操作メモ

PostgreSQLのJSON関数を使う機会があったのでメモします📝

#> 演算子

ネストしたJSONの値を取り出す。

-- テーブル例
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO users (data) VALUES
('{
    "name": "田中",
    "profile": {
        "age": 30,
        "address": {
            "city": "渋谷区"
        }
    }
}');

-- ネストした値の取得
SELECT data #> '{profile, address, city}' FROM users;
-- => "渋谷区"

-- WHERE句で使う
SELECT * FROM users 
WHERE data #> '{profile, address, city}' = '"渋谷区"';

#> はJSONBで返す。#>> だとテキストで返る。

jsonb_set

JSON内の特定の値を更新する。

jsonb_set(target, path, new_value, create_if_missing)

-- 年齢を更新
UPDATE users
SET data = jsonb_set(data, '{profile, age}', '31')
WHERE id = 1;

-- ネストした値も同じ
UPDATE users
SET data = jsonb_set(data, '{profile, address, city}', '"新宿区"')
WHERE id = 1;

-- 複数更新する場合はネストする
UPDATE users
SET data = jsonb_set(
    jsonb_set(data, '{profile, age}', '32'),
    '{profile, address, city}',
    '"目黒区"'
)
WHERE id = 1;

jsonb_build_array

配列を作る。

SELECT jsonb_build_array('TypeScript', 'React', 'PostgreSQL');
-- => ["TypeScript", "React", "PostgreSQL"]

参考