SQL ServerでJSON文字列を別行に分割してみた

SQL ServerでJSON文字列を含むカラムがある場合、ISJSON関数でJSON文字列かどうか判断でき、OPENJSON関数でJSON文字列を行分割することができる。

今回は、ISJSON関数やOPENJSON関数でJSON文字列を別行に分割してみたので、その手順を共有する。

前提条件

下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

JSON文字列の行分割操作

JSON文字列の行分割操作を行った結果は、以下の通り。

1) 以下のデータがある状態からスタートする。

SELECT * FROM user_data ORDER BY id ASC

なお、都道府県コードは、以下のサイトに記載の値と同じにしている。
https://webuip.nii.ac.jp/webuip/ill-map-jis.html

2) 以下のように、ISJSON関数の戻り値=1であるレコードを取得すると、memoがJSON文字列であるデータのみを抽出できる。

SELECT * FROM user_data
WHERE ISJSON(memo) = 1
ORDER BY id ASC

3) 以下のように、OPENJSON関数を利用すると、memoのJSON文字列(code, name)毎に別行に分割することができる。

SELECT u.id, u.name, memo_json.code, memo_json.name
FROM user_data u
CROSS APPLY OPENJSON(u.memo) WITH (
     code VARCHAR(2)
   , name NVARCHAR(10)
) memo_json
WHERE ISJSON(u.memo) = 1

4) 以下のように、3)で抽出された結果に抽出条件を、WHERE句に追加することもできる。

SELECT u.id, u.name, memo_json.code, memo_json.name
FROM user_data u
CROSS APPLY OPENJSON(u.memo) WITH (
     code VARCHAR(2)
   , name NVARCHAR(10)
) memo_json
WHERE ISJSON(u.memo) = 1
AND memo_json.name = '東京'

要点まとめ

• SQL ServerでJSON文字列を含むカラムがある場合、ISJSON関数でJSON文字列かどうか判断でき、OPENJSON関数でJSON文字列を行分割することができる。