SQL Server - OPENROWSETでJSONファイルを読み込んでOPENJSON
前回、OPENJSONを使ってJSON文字列を結果セットに変換してみました。
そのあとにそういえば「JSONファイル」を読み込んでOPENJSONを使う方法を知らないなと思いました。検索してみると次の記事が見つかります。
Importing JSON files into SQL Server using OPENROWSET (BULK) | SQL Server Database Engine Blog
この記事の二番煎じもいいところですが、OPENROWSETもCROSS APPLYもたぶんあまり使ったことがない気がするので試してみました。
まずJSONファイルを用意します。
{ "entries": [ { "id": 10, "name": "あ" }, { "id": 30, "name": "い" }, { "id": 20, "name": "う" } ] }
JSONファイルはC:\Temp\sample.jsonにあるとします。このファイルを読み込んで結果セットに変換するクエリは次のように書きます。
select Id, Name from -- OPENROWSETでJSONファイルを読み込む -- SINGLE_NCLOBを指定して1行1列の結果セットを取得する -- 結果セットにJson(Text)という名前を付ける openrowset(bulk N'C:\Temp\sample.json', SINGLE_NCLOB) as Json(Text) -- 結果セットのJson.Textに対してCROSS APPLYでOPENJSONを呼び出す cross apply openjson(Json.Text, N'$.entries') with( Id int N'$.id', Name nvarchar(10) N'$.name' ) order by Id; -- 実行結果 /* Id Name ----------- ---------- 10 あ 20 う 30 い */
OPENROWSETの引数にSINGLE_NCLOBを指定すると、ファイルの内容をnvarchar(max)の1行1列の結果セットとして取得できます。CROSS APPLYは結果セットの行ごとに関数を呼び出します。今回の結果セットは1行なのでOPENJSONは1回だけ呼ばれているはずです。
参考
SQL Server - OPENJSONは役に立つ
SQL Server 2016から手軽にJSONを扱えるようになっています。
MSDNなどを読みながら勉強している最中で全体を把握しているわけではないですが、SQL ServerのJSONサポートは次の2つの機能がメインなのかなと思います。
| 機能 | 概要 |
|---|---|
| FOR JSON句 | 結果セットをJSON文字列に変換する |
| OPENJSON関数 | JSON文字列を結果セットに変換する |
ここではSELECT文で取得できる行と列の表形式のデータを「結果セット」と言っています。たぶん言いますよね。行セットとも言うのかもしれません。
そしてOPENJSONの使い方を知っているとJSON文字列を解析したり、集計や抽出するときなどになかなか役に立つのかなと思うので、クエリを書きながら使い方を覚えていきたいと思います。
まずOPENJSONには
の2つがあります。
今回は明示的なスキーマを指定する方法を試します。正直なところ既定のスキーマはあまり使わないのかな？と。
明示的なスキーマを指定して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文字列をパースして取得できたオブジェクト」という解釈でいいのかなと思います。
ドル記号 ($) はコンテキスト アイテムを表します。
パス式を指定して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をごにょごにょしたいときに役に立つかなと思いました。
その他の参考
数値や文字列を右詰して前を「0（ゼロ）」で埋める（T-SQL）
T-SQLの小ネタです。
数値や文字列を右詰して前を「0（ゼロ）」で埋めた文字列を作ります。
数値の右詰と0埋め
数値の場合はSQL Server 2012から追加されているFORMAT関数を使います。C#と同じような書式を指定できます。
-- 3桁の数値を右詰と0埋めして5桁に declare @number int = 123; print format(@number, N'd5'); print format(@number, N'00000'); -- またはこれ -- 実行結果 -- 00123 -- 00123
SQL Server 2012以前は省略（・・・調べていません）。
参考
文字列の右詰と0埋め
文字列の場合はRIGHT関数を使って次のようにするといいみたいです。
-- 3桁の文字列を右詰と0埋めして5桁に declare @text varchar(3) = N'123'; print right('00000' + @text, 5); -- 実行結果 -- 00123
参考
数値や文字列を右詰して前を「0（ゼロ）」で埋める（C#）
C#の小ネタです。まぁいつも小ネタですが。
数値や文字列を右詰（右寄せ？）して前を「0（ゼロ）」で埋めた文字列を作ります。
数値の右詰と0埋め
数値の場合はstring.FormatメソッドかToStringメソッドで標準の10進数"D"数値書式指定を使います。"0"カスタム指定子を使ってもできますが標準のほうでいいのかな？と。
// 3桁の数値を右詰と0埋めして5桁に var number = 123; Console.WriteLine(string.Format($"{number:d5}")); // 標準の数値書式指定 Console.WriteLine(number.ToString("d5")); // ToStringを使うと Console.WriteLine(string.Format($"{number:00000}")); // カスタム数値書式指定 // 実行結果 // 00123 // 00123 // 00123
参考
文字列の右詰と0埋め
文字列の場合はstring.PadLeftメソッドを使います。
// 3桁の文字列を右詰と0埋めして5桁に var text = "123"; Console.WriteLine(text.PadLeft(5, '0')); // 実行結果 // 00123
参考
Entity Frameworkでenumのビット演算
FlagsAttributeを適用したenum（「ビットフラグ列挙体」と言うんですかね）に対して、Entity Frameworkでビット演算してみました。
ビット演算というよりHasFlagsメソッドを使えるよね？ということを確認した感じです。
エンティティとDBコンテキスト
いつものごとくエンティティとDBコンテキストを作ります。
// フラグ [Flags] public enum ItemFlags { Apple = 0x01, // 0000 0001 Pineapple = 0x02, // 0000 0010 Pen = 0x04, // 0000 0100 } // エンティティ public class Item { public int Id { get; set; } // フラグを持つ public ItemFlags Flags { get; set; } } // DBコンテキスト public class AppDbContext : DbContext { public IDbSet<Item> Items { get; set; } // 色々省略 }
データも用意
dbo.Itemテーブルを作ってデータを用意しておきます。
-- こんな感じ select * from dbo.Item; /* Id Flags ----------- ----------- 1 5 -- 0000 0101 2 1 -- 0000 0001 3 6 -- 0000 0110 */
取得してみる
HasFlagメソッドを使って取得してみます。
// _connectionStringとか色々省略 using (var dbContext = new AppDbContext(_connectionString)) { var items = dbContext.Items .Where(item => item.Flags.HasFlag(ItemFlags.Pen)) .ToList(); foreach (var item in items) { Console.WriteLine($"Id = {item.Id}, Flags = {item.Flags}"); } } // 実行結果 /* Id = 1, Flags = Apple, Pen Id = 3, Flags = Pineapple, Pen */
クエリはこんな感じです。
SELECT [Extent1].[Id] AS [Id], [Extent1].[Flags] AS [Flags] FROM [dbo].[Item] AS [Extent1] WHERE (( CAST( [Extent1].[Flags] AS int)) & ( CAST( 4 AS int))) = 4
HasFlagを使わない場合も。
using (var dbContext = new AppDbContext(_connectionString)) { var items = dbContext.Items .Where(item => (item.Flags & ItemFlags.Pen) == ItemFlags.Pen) .ToList(); foreach (var item in items) { Console.WriteLine($"Id = {item.Id}, Flags = {item.Flags}"); } } // 実行結果 /* Id = 1, Flags = Apple, Pen Id = 3, Flags = Pineapple, Pen */
クエリもこんな感じ。
SELECT [Extent1].[Id] AS [Id], [Extent1].[Flags] AS [Flags] FROM [dbo].[Item] AS [Extent1] WHERE 4 = (( CAST( [Extent1].[Flags] AS int)) & (4))
