SQL Server での JSON を表として扱う & インデックスのお話し #dekan

この前の 第8回 関西DB勉強会 - connpass で、
ついにリリース！！ MySQL 8.0 最新情報
こちらのお話しをされてたときに、 JSON_TABLE と Generated Column で JSON にインデックスを作る話しの箇所で会場が盛り上がってたので、 SQL Server でも同じこと出来るよーっていうお話し。

クエリ書いて紹介していくので、JSON が入ったテーブルを用意しときます。
一応 JSON しか入らないように CHECK制約掛けときます。

drop table if exists Table1
create table Table1 (
  loto6json nvarchar(200) not null 
    constraint C_Table1_JSON 
      check(isjson(loto6json) > 0)
)

insert into Table1 values 
('{"no":"第1回","date":"2000年10月5日","numbers":["02","08","10","13","27","30"],"bonus":"39"}'),
('{"no":"第2回","date":"2000年10月12日","numbers":["01","09","16","20","21","43"],"bonus":"05"}'),
('{"no":"第3回","date":"2000年10月19日","numbers":["01","05","15","31","36","38"],"bonus":"13"}'),
('{"no":"第4回","date":"2000年10月26日","numbers":["16","18","26","27","34","40"],"bonus":"13"}'),
('{"no":"第5回","date":"2000年11月2日","numbers":["09","15","21","23","27","28"],"bonus":"43"}')

最初は JSON_TABLE の方からいきましょう。
MySQL の JSON_TABLE はこんなのです。
MySQL :: MySQL 8.0 Reference Manual :: 12.16.6 JSON Table Functions
JSON を表形式にしてくれる感じなんですかね。

これと同等？の動きをするのは
OPENJSON (Transact-SQL) | Microsoft Docs
かな。
OPENJSON は テーブル値関数なので CROSS APPLY と併用する形になるかと。

select [no], [date], numbers, bonus 
from Table1 cross apply openjson(Table1.loto6json) 
  with (
    [no] nvarchar(10)
    , [date] nvarchar(11)
    , numbers nvarchar(max) '$.numbers' as json
    , bonus int
  )

これの結果がこんなのになります。

no	date	numbers	bonus
第1回	2000年10月5日	["02","08","10","13","27","30"]	39
第2回	2000年10月12日	["01","09","16","20","21","43"]	5
第3回	2000年10月19日	["01","05","15","31","36","38"]	13
第4回	2000年10月26日	["16","18","26","27","34","40"]	13
第5回	2000年11月2日	["09","15","21","23","27","28"]	43

numbers も分解したい場合はもう一回 OPENJSON 使う感じで。
データ多くなるので条件絞っときます。

select [no], [date], [value], bonus
from Table1 cross apply openjson(Table1.loto6json) 
  with (
    [no] nvarchar(10)
    , [date] nvarchar(11)
    , numbers nvarchar(max) '$.numbers' as json
    , bonus int
  ) nest1 cross apply openjson(nest1.numbers) 
  with ( [value] int '$' )
where [no] = N'第1回'

結果はこんな感じ。ちゃんと取れてますね。

no	date	value	bonus
第1回	2000年10月5日	2	39
第1回	2000年10月5日	8	39
第1回	2000年10月5日	10	39
第1回	2000年10月5日	13	39
第1回	2000年10月5日	27	39
第1回	2000年10月5日	30	39

次は Generated Column で JSON にインデックス の方にです。

MySQL でのお話しはここら辺っぽいです。
MySQL :: MySQL 8.0 Reference Manual :: 8.3.11 Optimizer Use of Generated Column Indexes

これと同等の動きをするのは 計算列でのインデックス ですね。
ドキュメントはこれかな？
Index JSON data | Microsoft Docs

SQL Server の計算列に対するインデックスについては、
Indexes on Computed Columns | Microsoft Docs
こちらをどうぞ。

では、計算列を作って index を貼ります。

alter table Table1 add 
  [date] as json_value(loto6json, '$.date')
create index idx_Table1_date on Table1([date])

実行すると警告出ますが無視します。

警告: 非クラスター化 インデックスの最大キー長は 1700 バイトです。インデックス 'idx_Table1_date' の最大長は 8000 バイトです。大きな値の組み合わせの一部では挿入操作または更新操作が失敗します。

計算列の結果が 8000 バイト超える場合は、エラーになってしまうよ！っていう警告です。今回は超えないのが分かっているので無視です。

データが少なすぎて index 使われないので適当にデータを増やしてからクエリを実行すると…

select loto6json from Table1 
where [date] = N'2000年10月5日'

こんな実行プランになりました。 Index Seek になってますね！

ちなみに 計算列 まで取ってしまうと、ここで計算します。

select * from Table1
where [date] = N'2000年10月5日'

ちなみに SQL Server の計算列には、PERSISTED オプションで永続化出来ますが、参照時に永続化の値を使うことは少なそうです。。
今回の例で試しましたが select で Compute Scalar してました。
調べると幾つか出てきますねー。
浮動小数点型の計算列でインデックスを貼りたい時と、パーティションテーブルで使う以外は PERSISTED は使わない感じかなー。
performance - Why does the Execution Plan include a user-defined function call for a computed column that is persisted? - Stack Overflow
Properly Persisted Computed Columns - SQLPerformance.com

メモ：Azure Log Analytics のドキュメント

メモ：Application Insights のデータを検索する時に

Azure Log Analytics

文字列 の条件とか
Azure Log Analytics - String operators

Utf8Json で日本語プロパティ？が入ってると Deserialize 時にデータが欠損する？

試したソースの commit は 7f7d6e30b65e56e250baa49f5becba64730e5227
再現するコードは書けたけど、何でプロパティ名変えただけで起きるのかは調べきれてない。。

using System;
using System.Runtime.Serialization;
using Xunit;

namespace Utf8Json.Tests
{
    public class JapaneseTest
    {
        [Fact] /* 失敗する */
        public void FailTest()
        {
            var m = new FailClass
            {
                番号 = 908,
                取引先名 = "あいうえお",
                担当者名 = "かきくけこ",
                日本語四 = "ABCDEFG",
                日本語五 = "てすと",
                日本語六 = new DateTime(2017, 10, 25, 8, 0, 0),
                日本語七 = new DateTime(2017, 10, 25, 17, 0, 0),
                日本語八 = 1
            };
            var mstr = JsonSerializer.ToJsonString(m);
            var desm = JsonSerializer.Deserialize<FailClass>(mstr);
            m.IsStructuralEqual(desm);
        }
        [Fact] /* 成功する */
        public void SuccessTest()
        {
            var m = new SuccessClass
            {
                番号 = 908,
                取引先名 = "あいうえお",
                日本語三 = "かきくけこ",
                日本語四 = "ABCDEFG",
                日本語五 = "てすと",
                日本語六 = new DateTime(2017, 10, 25, 8, 0, 0),
                日本語七 = new DateTime(2017, 10, 25, 17, 0, 0),
                日本語八 = 1
            };
            var mstr = JsonSerializer.ToJsonString(m);
            var desm = JsonSerializer.Deserialize<SuccessClass>(mstr);
            m.IsStructuralEqual(desm);
        }
    }

    [DataContract]
    public class FailClass
    {
        [DataMember]
        public int 番号 { get; set; }
        [DataMember]
        public string 取引先名 { get; set; }
        [DataMember]
        public string 担当者名 { get; set; }
        [DataMember]
        public string 日本語四 { get; set; }
        [DataMember]
        public string 日本語五 { get; set; }
        [DataMember]
        public DateTime? 日本語六 { get; set; }
        [DataMember]
        public DateTime? 日本語七 { get; set; }
        [DataMember]
        public int? 日本語八 { get; set; }
    }
    [DataContract]
    public class SuccessClass
    {
        [DataMember]
        public int 番号 { get; set; }
        [DataMember]
        public string 取引先名 { get; set; }
        [DataMember]
        public string 日本語三 { get; set; }
        [DataMember]
        public string 日本語四 { get; set; }
        [DataMember]
        public string 日本語五 { get; set; }
        [DataMember]
        public DateTime? 日本語六 { get; set; }
        [DataMember]
        public DateTime? 日本語七 { get; set; }
        [DataMember]
        public int? 日本語八 { get; set; }
    }
}

Logic App HTTP Trigger で ステータスコード が エラー でも後続処理を走らせる

Logic App で HTTP Trigger 設定したら、ステータスコード 4xx / 5xx が返ってくると後続の処理走らんのね…

調べてたらこれ見つけた。
Communicate with any endpoint over HTTP - Azure Logic Apps | Microsoft Docs
既定では、ステータスコード 300未満だと処理走らないって。

"conditions": [
  {
      "expression": "@greaterOrEquals(triggerOutputs()['statusCode'], 200)"
  }
],

これ入れといたら、200以上で後続の処理走るようになった。
まだコードからしか設定出来なさそうやけど、デザイナーで設定出来るようになったら良いねー。

色々調べたけど関係無かったやつもメモ代わりにリンク貼っとく。
Workflow triggers and actions - Azure Logic Apps | Microsoft Docs
Error and exception handling for Logic Apps in Azure | Microsoft Docs

4/21(土) 第８回 関西DB勉強会やります！

今月 21日(土) やります！
今回も色んな DB のお話しがありますので、是非ご参加を～。
kansaidbstudy.connpass.com

メモ：ASP.NET Core + App Service の Application Settings

メモ

public static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseApplicationInsights()
                .UseStartup<Startup>()
                .Build();

Configuration.GetConnectionString("DB");

appsettings.json

{
  "ApplicationInsights": {
    "InstrumentationKey": "xxxxxxxxxxxxxxxx"
  },
  "ConnectionStrings": {
    "DB": "xxxxxxxxxxxxxxxxxxxxx"
  }
}

って書いてるときの、App Service の Application Settings は、

• Application settings
  • ApplicationInsights__InstrumentationKey
• Connection strings
  • DB

でOK。

Connection strings は、環境変数に prefix 付くけど 「Configuration.GetConnectionString("DB");」で問題なく取れるらしい。
Working with Azure App Services Application Settings and Connection Strings in ASP.NET Core – cjAliaga Core
タイプ違いで複数同じ名称っていけるんやっけ？その場合はどうなるんやろ。

04/17(火) SQLWorld★大阪#46 開催します

SqlWorld :: SQLWorld★大阪#46 開催します。28回目の平日夜開催で、前回同様 ハンズオン 形式行う予定です。

【日時】
2018年04月17日（火曜日）　19:00~21:00
 

【イベント概要】
SQLWorld 28回目の平日夜開催～。今回も、みんなで SQL を書いてみようというハンズオン企画です！ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。
 

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0011 大阪府大阪市北区大深町 3番1号 グランフロント大阪タワーB（オフィス）
 

【参加費】
無料
 

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)
 

【参加可能人数】
13 人
 

お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を～。
今回は初心者向けの優しめの問題を考え中です。

開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。