🐘
PostgreSQL でエスケープされた JSON 文字列から JSON オブジェクトへ変換する
アプリケーションがエスケープされた JSON 文字列を PostgreSQL の JSONB 列に書き込んでしまったと仮定しましょう。その値は次のようになります。
"{\"type\": \"message\"}"
これは JSONB 列のルートレベルにただの文字列がある状態です。しかし、本当に欲しいのは次のような形です。
{"type": "message"}
しかし、すでにミスは起こってしまっているのでデータを修正するためのマイグレーションを行う必要があります。
マイグレーション
マイグレーションには次のように #>> 演算子を用います。
(metadata #>> '{}')::jsonb
PostgreSQL の #>> 演算子は「指定したパスの JSON オブジェクトをテキストとして取得」します。ここでは、ルートレベルのエスケープされていない文字列をテキスト値として PostgreSQL に返すように、空のパスを渡しています。その後、そのテキスト値を ::jsonb を用いて JSON へキャストすることができます。
これを基に、テーブルが events テーブルだった場合の更新クエリは以下になります。
UPDATE events SET metadata = (metadata #>> '{}')::jsonb;
このクエリを用いることでエスケープされた JSON 文字列から JSON オブジェクトへ変換しデータを更新することができます。このクエリはすでに JSON オブジェクトが入ってるレコードには影響がないため、仮に DB の中身がエスケープされた JSON 文字列と JSON オブジェクトの両方が入っていたとしても有効です。
次の SELECT 文を用いて事前に確認することをお勧めします。
SELECT (metadata #>> '{}')::jsonb FROM events;
Discussion