SQLServerでJSONの操作をする

最近業務でSQLServerのJSONデータを操作することがありましたので、備忘録代わりに残しておきます。

なお、構成や表記など見づらいかもしれませんが、
初投稿なのでご容赦ください。精進します。

参考にしたページは 参考ページ 参照

環境

Microsoft SQL Server 2022 - 16.0.1121.4
互換レベル160

使用テーブルの作成

CREATE TABLE emp_data (
    employee_id INT PRIMARY KEY,
    employee_data NVARCHAR(MAX) -- JSONデータを格納
);

--データ投入
INSERT INTO emp_data (employee_id, employee_data)
VALUES 
(1, N'{
    "name": "Taro",
    "age": 30,
    "skills": ["SQL", "C#", "Azure"],
    "address": {
        "city": "Osaka",
        "prefecture": "Osaka"
    }
}'),
(2, N'{
    "name": "Jiro",
    "age": 25,
    "skills": ["JavaScript", "HTML", "CSS"],
    "address": {
        "city": "Kobe",
        "prefecture": "Hyogo"
    }
}');

SQLServer2022ではJSON型の指定ができないので、NVARCHARを利用しています。
入るデータを厳密にJSONにしたいのであれば、CHECK制約で実現できます。

ALTER TABLE emp_data
ADD CONSTRAINT chk_employee_data CHECK (
        ISJSON(employee_data) = 1
    );

ISJSON : 文字列に有効な JSON が含まれているかどうかをテストします。

データ

employee_id	employee_data
1	{ "name": "Taro", "age": 30, "skills": ["SQL", "C#", "Azure"], "address": { "city": "Osaka", "prefecture": "Osaka" }}
2	{ "name": "Jiro", "age": 25, "skills": ["JavaScript", "HTML", "CSS"], "address": { "city": "Kobe", "prefecture": "Hyogo" }}

JSONデータの追加

JSONデータの階層追加

既存のJSONデータに新しい階層構造を追加する例です。

階層の追加

--追加したい値を一度出して
DECLARE @json_employee_data NVARCHAR(MAX) =
    (SELECT employee_data FROM emp_data WHERE employee_id = 1);

-- contact を追加して
DECLARE @json_inserted NVARCHAR(MAX) =
    JSON_MODIFY(@json_employee_data,'append $.contact', JSON_QUERY('{"phone" : "080-1234-5678","email": "[e-mail]" }')
);

--データ上書き
UPDATE emp_data
SET employee_data = @json_inserted
WHERE employee_id = 1;

JSON_MODIFYは文字列をプレーンテキストとして渡すため、
直接記載すると追加後の値にエスケープ文字(\)が入ってしまいます。

ダブルクォーテーションにエスケープ文字が入る例

UPDATE emp_data
SET employee_data = JSON_MODIFY(employee_data,
                                'append $.contact', 
                                '{"phone": "080-1234-5678",
                                  "email": "[e-mail]"}'
                    )
WHERE employee_id = 1;

JSONデータの配列への追加

既存の配列に新しい要素を追加する例です。

配列の追加

-- Skillに "Python" を追加
UPDATE emp_data
SET employee_data = JSON_MODIFY(employee_data, 'append $.skills', 'Python')
WHERE employee_id = 1;

JSONデータの更新

JSONデータの要素更新

配列内の特定の要素を更新する例です。

要素の更新

DECLARE @newSkill NVARCHAR(100) = 'TypeScript';

-- JavaScript → TypeScript に変更
UPDATE emp_data
SET employee_data = JSON_MODIFY(employee_data, '$.skills[0]', @newSkill)
WHERE employee_id = 2;

JSONデータの削除

JSONデータの階層削除

JSONデータ内の特定の階層を削除する例です。

階層の削除

UPDATE emp_data
SET employee_data = JSON_MODIFY(employee_data, '$.address', NULL)
WHERE employee_id = 1;

参考ページ