PostgreSQLのjson型カラムでマスキング処理をしてみた

はじめに

PostgreSQLのjson型カラムで特定のデータをマスキングしたい時に困ったので、その時に対応した内容の雑なメモ書きです。
ここでは、大文字の JSON はその書式やデータを表し、PostgreSQL の型を指す場合に json や jsonb のように小文字を使って区別します。

json型とjsonb型

PostgreSQL json 更新 と検索すると、とにかく検索でひっかかるjsonb型。
jsonb型とはなんなのかが全くわからない自分がいました。
歴史やPostgreSQLにいつ入れられたかは詳しい記事があると思うので詳細はそちらに任せるとして、ここではざっくりとした違いを説明したいと思います。
jsonb型とは、JSON テキストをバイナリ表現に変換して保持するデータ型のことで、
json型は、JSON テキストをそのまま文字列として保持しただけのものです。
結局json型の実態はJSONテキストなので、特定のデータを参照したい場合、都度JSONテキストをパースする必要があります。
jsonb型 では入力時にのみ JSON テキストを解析し、正規化されたバイナリ書式でカラムに格納するので、json型よりもデータアクセスが速いのが特徴のようです。

参考資料: http://scm.zoomquiet.top/data/20150213210641/index.html

json型のカラムで値をマスキングする

前提条件

今回使うバージョンはPostgreSQL12を使います。

概要

PosgreSQLのjson型/jsonb型で使える関数・演算子はほぼ一緒ですが、
唯一違うのはjsonb型にしかない演算子があることです。
このjsonb型でしか使えない演算子を使えば、JSONをパースする必要がなくてすごく楽にデータ更新等の作業ができるので、こちらをつかってマスキング処理をしていきます。

参考資料: https://www.sraoss.co.jp/PostgreSQL/Manual/document/12/functions-json.html

dockerでPostgreSQL立ち上げ

bash

$ docker run --rm --name postgres -d -e POSTGRES_PASSWORD=test  postgres:12
$ docker exec -it postgres bash
root@6820ff8c0b20:/# psql -U postgres
psql (12.6 (Debian 12.6-1.pgdg100+1))
Type "help" for help.

postgres=#

テーブル作成

SQL

postgres=# CREATE TABLE test (
    id SERIAL,
    json_vaule JSON,
    jsonb_vaule JSONB 
);

Insert

JSON作成関数は何個かあるのですが、
2つの別々の配列からキーと値の対を取る json_object を使います。

SQL

postgres=# INSERT INTO test (
    json_vaule,
    jsonb_vaule
) VALUES (
    json_object('{a, b}', '{1,2}'),
    json_object('{a, b}', '{1,2}')
);

postgres=# select * from test;
 id |       json_vaule       |     jsonb_vaule
----+------------------------+----------------------
  1 | {"a" : "1", "b" : "2"} | {"a": "1", "b": "2"}
(1 row)

マスキング

はじめにレコードの値を更新してマスキングする方法を探してました。
json型/jsonb型の要素の一部を変更する場合なら || を使うのが便利です。
ただし、json型に || をすると、そんな演算子がないとエラーがでます。

SQL

postgres=# UPDATE
    test
SET
    json_vaule = json_vaule || json_build_object(
        'a', XXXX
    )::jsonb
WHERE
    id = 1;
ERROR:  operator does not exist: json || jsonb
LINE 4:     json_vaule = json_vaule || json_build_object(
                                    ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

この場合は、json型をjsonb型にキャストして、実行します。
そうすれば、json型でもjsonb演算子を使って値の更新ができます。

postgres=# UPDATE
    test
SET
    json_vaule = json_vaule::jsonb || json_build_object(
        'a', 'XXXX'
    )::jsonb
WHERE
    id = 1;
UPDATE 1

postgres=# select * from test;
 id |     json_vaule     |     jsonb_vaule
----+--------------------+----------------------
  1 | {"a": "XXXX", "b": "2"} | {"a": "1", "b": "2"}
(1 row)

もちろんjsonb型だとわざわざキャストしなくても、そのまま || を使うことができます。

SQL

postgres=# UPDATE
    test
SET
    jsonb_vaule = jsonb_vaule || json_build_object(
        'a', 'XXXX'
    )::jsonb
WHERE
    id = 1;
UPDATE 1

postgres=# select * from test;
 id |       json_vaule        |       jsonb_vaule
----+-------------------------+-------------------------
  1 | {"a": "XXXX", "b": "2"} | {"a": "XXXX", "b": "2"}
(1 row)

jsonフィールドの値がレコードごとに異なる場合にこの置換方法で、ちゃんとマスキングされるかも検証しました。
まずはデータの作成をします。

SQL

postgres=# INSERT INTO test (
    json_vaule,
    jsonb_vaule
) VALUES (
    json_object('{c}', '{3}'),
    json_object('{c}', '{3}')
);

postgres=# INSERT INTO test (
    json_vaule,
    jsonb_vaule
) VALUES (
    json_object('{a}', '{4}'),
    json_object('{a}', '{4}')
);

postgres=# select * from test;
 id |       json_vaule        |       jsonb_vaule
----+-------------------------+-------------------------
  1 | {"a": "XXXX", "b": "2"} | {"a": "XXXX", "b": "2"}
  2 | {"c" : "3"}             | {"c": "3"}
  3 | {"a" : "4"}             | {"a": "4"}
(3 rows)

そして、 json_vaule に対して更新をかけます。
そうすると id: 2 の値は "c": "3" のみだったのが、マスキングしたい値 "a": "XXXX"が入ってしまいました。 || は 2つのjsonb値を結合して、新しいjsonb値を作るjsonb演算子です。既存のjsonフィールドに "a": "XXXX" を結合して新たなjsonフィールドを作るので、値がなければ追記してしまいます。

SQL

postgres=# UPDATE
    test
SET
    json_vaule = json_vaule::jsonb || json_build_object(
        'a', 'XXXX'
    )::jsonb;
UPDATE 3

postgres=# select * from test;
 id |       json_vaule        |       jsonb_vaule
----+-------------------------+-------------------------
  2 | {"a": "XXXX", "c": "3"} | {"c": "3"}
  3 | {"a": "XXXX"}           | {"a": "4"}
  1 | {"a": "XXXX", "b": "2"} | {"a": "XXXX", "b": "2"}
(3 rows)

やりたいことは a の値があればマスキングするということなので、 a がなければ何もしないが正解です。そのため、更新するのではなく、 a があれば削除することにしました。
jsonの要素を評価して削除する場合は、jsonb演算子の #- を使うと楽に特定の要素を削除できます。
json_vaule で更新をかけた結果、 a の値が削除され、それ以外は影響をうけません。
思っていたマスキングとは違いますが、見せたくない値を見せなくするということはできます。

SQL

postgres=# UPDATE
    test
SET
    json_vaule = json_vaule::jsonb #- '{a}';
UPDATE 3

postgres=# select * from test;
 id | json_vaule |       jsonb_vaule
----+------------+-------------------------
  2 | {"c": "3"} | {"c": "3"}
  3 | {}         | {"a": "4"}
  1 | {"b": "2"} | {"a": "XXXX", "b": "2"}
(3 rows)

おわりに

json型をSQLで扱ったことがないので、json型の演算子や関数の挙動がわからず苦労しました。
この内容を調査していて思ったのは、RDBMSでjsonを扱うのは辛いということだけはわかりました。