Shoeisha Technology Media

EnterpriseZine(エンタープライズジン)テクノロジーでビジネスを加速するための実践Webメディア

テーマ別に探す

デベロッパーの皆様、SQL Server - JSON 始めました (後編)

2015/10/19 06:00

 こんにちは。プレミア フィールド エンジニアの笹木です。前編に引き続き、SQL Server 2016 の JSON 対応について見ていきたいと思います。後編では、JSONの値を SQL Server の表形式データとして格納する「OPENJSON」と JSON に関わる組み込み関数に焦点をあてます。尚、この後編で説明する機能は、SQL Server 2016 CTP 3から利用可能になる予定です。

OPENJSON で JSON を取り込む

 前編では、SQL Server の表形式の入力を JSON フォーマットで出力する「FOR JSON」を説明しましたが、「OPENJSON」はその逆です。実際の例を見てもらった方が早いでしょう。以下は JSONのキーと値を OPENJSON のパラメーターとして渡した場合の例です。

 SELECT の結果セットとして、key 列に JSON のキーが、value 列に JSON の値が格納されています。FROM 句に記載した「OPENJSON」のパラメーターとして渡した JSON フォーマットが表形式の結果に変換されている、ということです。結果セットの列名は 「key」「value」「type」の 3 つです。key 列は nvarchar(4000)、value 列は nvarchar(max)、type 列は tinyint 型として返されます。XML を SQL Server テーブルに取り込む際は「XML データ型」が使用できますが、JSON では JSONそのままの形で SQL Server に格納する「JSONデータ型」は用意されていません。

 type 列は value 列がどのような型か表します。上記例の場合、value 列には「値」という文字列 (string) が格納されているため、type 列は文字列を示す 1 になります。その他にも type 列には整数であれば 2 が、配列であれば 4 が次の例のように格納されます。

 一般的には、入力となる JSONの各値がどのような型であるかは事前に分かっており、type 列の値が必要となるケースは多くないと思います。ただ、JSON の入力値が動的に変化する場合は type 列の値を基に分岐処理を行えます。

 例では SELECT で結果取得のみ行っていますが、SELECT した結果を SELECT INTO もしくは INSERT INTO SELECT でテーブル データとして格納できます。

OPENJSON の出力を整形する

 業務アプリケーションで JSON を使用する場合、value の中にオブジェクトが何階層かネストされていたり、JSON 全体が数十行・数百行あったりすると思います。その場合、「JSON の特定の値のみデータベースに格納したい」「オブジェクトの値を key、value 列ではなく、それぞれ任意の列として出力したい」と思われるかもしれません。次は、その要望に応える機能を説明したいと思います。

 まずは、JSON に含まれる特定の値のみ出力したい場合の対処です。OPENJSON はパラメーターを 2つ受け取ることができます。1つ目は今まで見てきた通り、JSON フォーマットの文字列を受け取ります。2つ目のパラメーターは、どの部分を出力対象とするかを指定するパスを受け取ります。

 次の例の場合、”会員” というキーに “住所” オブジェクトがネストしています。”住所” オブジェクト内の値をそれぞれ取得したい場合、OPENJSON の 2 つ目のパラメーターにパスを記載することで該当部分のみ取得できます。『lax $.”会員”.”住所”』のうち、「$」は JSON フォーマットのルートを示します。つまり、ルート直下の “会員” というオブジェクトにネストされている “住所” オブジェクトを指定しています。「lax」というのはパスの表記モードを示します。もし、指定したパス (今回の場合は $.”会員”.”住所”) が存在しなかった場合、lax モードの場合は空の結果セットが返ってきてエラーにはなりません。lax モードの他に strict モードがあり、strict モードの際にパスが不正であった場合はエラーが返ります。尚、パス指定のモードは省略できます。省略した場合、既定で lax モードが選択されます。

 次は、出力を任意の列に変更する対処についてです。OPENJSON は WITH 句を追加して出力を指定できます。

 WITH 句がない場合は 「key」「value」「type」の列が結果セットとして返りますが、WITH 句を指定すると、例えば次の場合、「氏名」「都道府県」列が結果セットとして返ります。

 ここまでの内容から、JSON フォーマットの指定した範囲の値を指定した列として取得して SQL Server に表形式で格納できることが分かります。

※この続きは、会員の方のみお読みいただけます(登録無料)。


著者プロフィール

  • 笹木幸一郎(ササキコウイチロウ)

    日本マイクロソフト株式会社 / Microsoft Corporation Global Black Belt Analytics Technology Lead   SQL Server を中心としたデータプラットフォーム製品のサポート経...

バックナンバー

連載:ここから始めよう SQL Server 2016 新機能

もっと読む

この記事もオススメ

All contents copyright © 2006-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5