TSONによるJSONとそのネストされたオブジェクトのクエリ

リレーショナルDBで動的にモデル化されたデータを格納することに直面すると、SQL ServerのJSON列に格納することにしました.
The well documented Recorsesは、初期の開発の間、便利になりました.
いくつかの回避策

ランタイムJSON逆シリアル化

全体のアプリケーションがEntity Framework で実行される間、ストア手順を使用して回避
今日まで、私はSQLを持つJSONオブジェクトのネストされた配列を問い合わせませんでした.それは、私がOPENJSONと呼ばれるT - SQLの別の方法につまずいた時です

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.

つまり、OpenJSONはJSONドキュメントに対して行セットビューを提供します.明示的に行セットの列とJSONプロパティパスを明示的に指定できます.OpenJSONが一連の行を返すので、T - SQLステートメントのFROM句にOpenJSONを使用することができます.これは、他のテーブル、ビュー、またはテーブル値関数を使用することができます.

少数サンプル

このサンプルJSONデータ

DECLARE @json NVARCHAR(MAX) = '{
    "firstName":"James",
    "lastName":"Doe",
    "ChildrenDetails":[
        {
            "Id":1,
            "Name":"John Doe",
            "Languages":["English", "German"]
        },
        {
            "Id":2,
            "Name":"Jane Doe",
            "Languages":["English"]
        },
        {
            "Id":3,
            "Name":"June Doe",
            "Languages":["German", "Tamil"]
        }]
}'

1 .配列への問い合わせ(子の細部)

SELECT 
    JSON_VALUE(d.value,'$.Id') AS Id,
    JSON_VALUE(d.value,'$.Name') AS Name
FROM OPENJSON(@json,'$.ChildrenDetails') AS d

ネストされた配列を平らにする

SELECT 
JSON_VALUE(d.value,'$.Id') AS Id,
JSON_VALUE(d.value,'$.Name') AS Languages,
l.value AS Name
  FROM OPENJSON(@json,'$.ChildrenDetails') AS d CROSS APPLY OPENJSON (d.value,'$.Languages') AS l

うまくいけば、複雑なJSON構造でこれを試すことができます.ユースケースは多いですが、今日は私たちにとって便利な機能になりました😃
去る前に
EOF