SQL Server - OPENJSONは役に立つ

sql-server t-sql

SQL Server 2016から手軽にJSONを扱えるようになっています。

MSDNなどを読みながら勉強している最中で全体を把握しているわけではないですが、SQL ServerのJSONサポートは次の2つの機能がメインなのかなと思います。

機能 概要
FOR JSON句 結果セットをJSON文字列に変換する
OPENJSON関数 JSON文字列を結果セットに変換する

ここではSELECT文で取得できる行と列の表形式のデータを「結果セット」と言っています。たぶん言いますよね。行セットとも言うのかもしれません。

そしてOPENJSONの使い方を知っているとJSON文字列を解析したり、集計や抽出するときなどになかなか役に立つのかなと思うので、クエリを書きながら使い方を覚えていきたいと思います。

まずOPENJSONには

の2つがあります。

OPENJSON (TRANSACT-SQL)

今回は明示的なスキーマを指定する方法を試します。正直なところ既定のスキーマはあまり使わないのかな？と。

明示的なスキーマを指定してOPENJSON

明示的なスキーマを指定するとは、結果セットで取得したいスキーマをWITH句で指定することです。WITH句を使わないと既定のスキーマになります。

早速クエリを。

-- JSON文字列
declare @json nvarchar(max) = N'[
  { "id": 10, "name": "Aaa" },
  { "id": 30, "name": "Ccc" },
  { "id": 20, "name": "Bbb" },
  { "id": 99, "name": "Xxx" }
]';

-- OPENJSONを使ってJSON文字列を結果セットに
select *
from openjson(@json)
with(   -- 結果セットのスキーマ（Id, Nameカラムを持つスキーマ）を指定する
    Id int N'$.id',    -- "id"プロパティをIdカラムとする
    Name nvarchar(10) N'$.name'   -- "name"プロパティをNameカラムとする
)
order by Id;

-- 実行結果
/*
Id          Name
----------- ----------
10          Aaa
20          Bbb
30          Ccc
99          Xxx
*/

OPENJSONの引数にはJSON文字列を渡します。

WITH句では結果セットのスキーマをカラムごとにカラム名、データ型、JSONパス式の順で指定します。CREATE TABLEのカラム定義に似ていますね。指定したカラムは普通のテーブルのカラムのようにORDER BY句などで利用できます。

この中で見慣れないのはJSONパス式かなと思いますが、ざっくり言うとJavaScriptオブジェクトのプロパティアクセスのような文字列です。

JSON パス式 (SQL Server)

ドル記号（$）については以下の記述がありますが、コンテキストアイテムは「JSON文字列をパースして取得できたオブジェクト」という解釈でいいのかなと思います。

ドル記号 ($) はコンテキスト アイテムを表します。

パス式を指定してOPENJSON

OPENJSONは2つ目の引数にJSONパス式を渡すこともできます。JSONパス式を渡すと取得するオブジェクト（コンテキストアイテム）を指定できます。

declare @json nvarchar(max) = N'{
  "entries": [
      { "id": 10, "name": "Aaa" },
      { "id": 30, "name": "Ccc" },
      { "id": 20, "name": "Bbb" },
      { "id": 99, "name": "Xxx" }
  ]
}';

-- "entries"プロパティが指す配列を取得したいとして
select *
-- OPENJSONの2つ目の引数に取得したいオブジェクトへのJSONパスを指定
from openjson(@json, N'$.entries')
with(
    Id int N'$.id',
    Name nvarchar(10) N'$.name'
);

-- 実行結果
/*
Id          Name
----------- ----------
10          Aaa
30          Ccc
20          Bbb
99          Xxx
*/

とりあえずこんなところで。

OPENJSONはJSONをごにょごにょしたいときに役に立つかなと思いました。

その他の参考