SQL ServerでJSON文字列の値の取得・設定を行なう

SQL Server 2016からJSON用の関数がいくつか追加されていますので、使い方を備忘録にしておきます。

ここでは以下のようにカラムにJSONの値が入っているテーブルを例にします。

値がJSONフォーマットかどうか

値がJSONフォーマットかどうか確認するには、ISJSON関数を使います。返り値が1ならJSONフォーマットです。

SELECT ISJSON(JSON_COLUMN)
FROM TEST
WHERE ID = 1

JSON文字列から値を取得する

JSONの値を取り出すには、[JSON_VALUE関数]'(https://docs.microsoft.com/ja-jp/sql/t-sql/functions/json-value-transact-sql?view=sql-server-2017)を使います。
この関数は2つの引数を取り、第1引数がJSON文字列、第2引数がどこの値を取り出すかを指定するパス式です。$.ownerとすればownerの値が、$.bag.goods[1]とすればgoodsの1番目の値が取り出せます。

SELECTでもWHEREでも使うこともできます。

SELECT
    ID,
    JSON_VALUE(JSON_COLUMN, '$.owner')
FROM TEST

sizeが5以上のID(つまり1のみ)が抽出されます。

SELECT ID
FROM TEST
WHERE JSON_VALUE(JSON_COLUMN, '$.bag.size') > 5

goodsの1番目の値を取り出すのは以下のように書きます。値が無い場合、NULLとなります。

SELECT
    ID,
    JSON_VALUE(JSON_COLUMN, '$.bag.goods[1]')
FROM TEST

JSON文字列に値をセットする

JSONの値を編集することもでき、この場合はJSON_MODIFY関数を使います。第1引数はJSON文字列、第2引数はパス式、第3引数は変更後の値です。

例えば、ID=1のownerを"suzuki"に変更したい場合は、こんなかんじです。

UPDATE TEST
SET JSON_COLUMN = JSON_MODIFY(JSON_COLUMN, '$.owner', 'suzuki')
WHERE ID = 1

値を追加する場合、パス式の先頭にappendを記述します。ID=2のgoodsに"wallet"を追加してみます。

UPDATE TEST
SET JSON_COLUMN = JSON_MODIFY(JSON_COLUMN, 'append $.bag.goods', 'wallet')
WHERE ID = 2

値を削除する場合は、第3引数にNULLを設定します。

UPDATE TEST
SET JSON_COLUMN = JSON_MODIFY(JSON_COLUMN, '$.bag.size', NULL)
WHERE ID = 2

こういうお便利関数が定義されると、DBのカラムに気兼ねなくJSON文字列を突っ込めますね。