PostgreSQLでの JSONの深いキーの更新など
postgresql で JSON/JSONB を扱うことができますが、以下のようなJSON を更新するときの課題です。
{"test": {"test2":{ "test3": "hoge"}}}
JSON_SET や JSON_SET_LAX を使用した場合
JSON_SET や JSON_SET_LAX は既存のJSONデータを破壊せずに更新できるクエリーです。JSONB_SET_LAX のみ説明すると
text_json->'test'->'test2'->'test3' というキーのJSON を 'hoge' という値にしたい場合
UPDATE t_test
SET test_json = JSONB_SET_LAX(
test_json ::jsonb,
'{test, test2, test3}',
'"hoge"'::jsonb,
TRUE,
'use_jsonb_null'
) WHERE test_id='3087'
JSONが以下の状態であれば追加・更新できますが
{"test": {"test2":{ "test3": "test"}}}
{"test": {"test2":{}}}
JSONが以下の状態だと追加・更新されません。何も起きないという感じになります。
{"test": {}}
{}
つまり 'test2' のキーが存在してない場合は test3 は更新・追加されません。
*第4引数が true の場合はキーがつくられるとおもいきや 最後のキー(test3)の1つ前のキー(test2)は存在している必要があるようです。
で、力技で、第1引数 を以下のようにして・・・・
COALESCE(test_json, '{}'::jsonb) || '{"test": {"test2": {}}}'::jsonb,..."
・・・でいけるはいけます・・・
UPDATE t_test
SET test_json = JSONB_SET_LAX(
COALESCE(test_json, '{}'::jsonb) || '{"test": {"test2": {}}}'::jsonb,
'{test, test2, test3}',
'"hoge"'::jsonb,
TRUE, 'use_jsonb_null'
)
WHERE test_id='3087'
連想配列っぽく
そこでLLMにきいてもいい案がでなく、ぐぐっていたら見つけました。
直観的に全て解決できそうなクエリーです。
UPDATE t_test
SET test_json['test']['test2']['test3']= '"Wine"'
WHERE test_id='3087'
以下のように プログラムの連想配列に値を設定するかのように設定すると
キーのあるなし関係なく設定が可能です!
SET test_json['test']['test2']['test3']= '"Wine"'
うれしいことに test_json が NULL であっても ちゃんと値が設定されました
編集する
