SQL Server 2016 をJSON形式で操作

2017.09.27

こんにちは。YDCの やす です。

今回はSQL Server 2016についての記事です。「動的データマスク」や「クエリストア」等の新機能に隠れて
あまり知られていませんが、SQL Server 2016から 「JSONサポート」機能 が実装されています。
今回は 「JSONサポート」機能 で色々と操作をしてみましたので紹介します!
JSON形式でデータのやりとりをしているシステムは多いと思いますので是非活用してみて頂ければと思います!

環境
OS SQL Server
Windows Server 2016 Standard SQL Server 2016 Standard

テーブルの準備

今回の検証に使うテーブルとサンプルデータの事前準備をします。

1> use mydb
2> go

1> create table animal (
2> no decimal(2),
3> name varchar(30),
4> cry varchar(10))
5> go

1> insert into animal values(1,"dog","wan")
2> insert into animal values(1,"cat","nya!")
3> go

(1 行処理されました)

(1 行処理されました)

1> select * from animal
2> go
no name cry
---- ------------------------------ ----------
1 dog wan
1 cat nya!

(2 行処理されました)
1>

「JSONサポート」機能を活用

1.JSON形式で問い合わせ結果を取得

上記のテーブルの問い合わせ結果をJSON形式で取得してみましょう。
取得するにはテーブル名の後ろに「for json path」と追記するだけです。

1> select * from animal
2> for json path
3> go
JSON_F52E2B61-18A1-11d1-B105-00805F49916B
---------------------------------------------------------------------------
[{"no":1,"name":"dog","cry":"wan"},{"no":2,"name":"cat","cry":"nya!"}]★JSON形式で取得

(1 行処理されました)
1>

1行のJSON形式のデータを取得することができました。問い合わせ結果はJSON形式にフォーマットされ、
データの値はJSONオブジェクトのvalue、カラム名はkeyとして生成されます。

む!しかし問い合わせ結果にルート要素がついていません。無名の配列では思うように
パースできず、困ってしまう場面が出てきそうです。

ルート要素を付けるにはROOTオプションを使用します。

1> select * from animal
2> for json path,root('animal')
3> go
JSON_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------------------------------------------------
{"animal":[{"no":1,"name":"dog","cry":"wan"},{"no":2,"name":"cat","cry":"nya!"}]}★ルート要素がつきました

(1 行処理されました)
1>

root('<ルート要素名>')のオプションを末尾につけることで簡単にルート要素をつけることができました!
ちなみにドット区切りで列名を指定することで、子要素を持つ複数階層で出力することもできます。

1> select
2> no,
3> name as 'animal.name',★animalを親要素、子要素をnameとします。
4> cry as 'animal.cry'★animalを親要素、子要素をcryとします。
5> from animal
6> for json path
7> go
JSON_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------
[{"no":1,"animal":{"name":"dog","cry":"wan"}},{"no":2,"animal":{"name":"cat","cry":"nya!"}}]

(1 行処理されました)
1>

簡単に複数階層で出力することができました!


2.JSON形式のデータを行と列に変換

テーブルの問い合わせ結果をJSON形式に変換するだけではありません。
標準で用意されているOPENJSON関数を使用することで、JSON形式のデータをテーブルに対して
問い合わせしているかの様に、行と列の形式で結果を取得することもできます。
下記では変数「animal_json」にJSON形式のデータを代入し、OPENJSON関数で問い合わせてみました。

1> DECLARE @animal_json NVARCHAR(MAX)
2> SET @animal_json =
3> N'[
4~ {"no":3,"name":"cow","cry":"mo-!"},{"no":2,"name":"bird","cry":"pi-!"}
5~ ]'★JSON形式のデータをSET
6>
7> select *
8> from OPENJSON(@animal_json)
9> WITH (no int' strict $.no',
10> name varchar(10) 'strict $.name',
11> cry varchar(10) 'strict $.cry')★OPENJSON関数でデータに問い合わせ
12> go
no name cry
----------- ---------- ----------
3 cow mo-!
2 bird pi-!

(2 行処理されました)
1>

行と列の形式でJSON形式のデータから結果を取得することができました。
オプションの WITH 句に出力する要素を'strict $.<要素名>'で指定します。

上記の例では単純なJSON形式のデータを使用しましたが、子要素を持つ複数階層の
データからも'strict $.<親要素>.<子要素>'で指定することで値を取り出すことができます。

1> DECLARE @animal_multjson NVARCHAR(MAX)
2> SET @animal_multjson =
3> N'[
4~ {
5~ "no":5,
6~ "animal":{
7~ "kind":"bird",
8~ "type":{
9~ "class":"Chicken",
10~ "cry":"Cokeco-!"
11~ },
12~ "color":"white"
13~ }
14~ },
15~ {
16~ "no":6,
17~ "animal":{
18~ "kind":"bird",
19~ "type":{
20~ "class":"Sparrow",
21~ "cry":"chun!"
22~ },
23~ "color":"brown"
24~ }
25~ }
26~ ]'
27> select *
28> from OPENJSON(@animal_multjson)
29> WITH ( no int 'strict $.no',
30> kind varchar(10) 'strict $.animal.kind',
31> animal_type varchar(10) 'strict $.animal.type.class',★子要素に対して問い合わせ
32> cry varchar(10) 'strict $.animal.type.cry',★子要素に対して問い合わせ
33> color varchar(10) 'strict $.animal.color'
34> )
35> go
no kind animal_type cry color
----------- ---------- ----------- ---------- ----------
5 bird Chicken Cokeco-! white
6 bird Sparrow chun! brown
★子要素の取得を確認できました!
(2 行処理されました)
1>

上記は複数階層のJSON形式のデータから値を取得する例になります。

しかし複雑な文字列になってくるとJSON形式のデータが正しい形式なのか不安になります...。
そんな時にISJSON関数を使用することでデータがJSON形式かどうか判断することができます。

1> DECLARE @animal_json NVARCHAR(MAX)
2> SET @animal_json =
3> N'[
4~ {"no":3,"name":"cow","cry":"mo-!"},{"no":2,"name":"bird","cry":"pi-!"}
5~ ]'
6>
7> select ISJSON(@animal_json)★JSON形式か確認
8> go

-----------
1

(1 行処理されました)
1> DECLARE @animal_json NVARCHAR(MAX)
2> SET @animal_json =
3> N'[
4~ hogehoge
5~ ]'
6>
7> select ISJSON(@animal_json)
8> go

-----------
0★JSON形式ではないため0を返す

(1 行処理されました)
1>

ISJSON関数は文字列がJSON形式の時は1、そうでない時は0を返します。
OPENJSON関数を使用する前にISJSON関数でチェックした方が良さそうです。


3.JSON形式のデータをテーブルに挿入

OPENJSON関数活用して、SELECT・INSERTすることでJSON形式のデータをテーブルに
挿入することができます。SELECT・INSERTも試してみたので確認してみましょう。

1> DECLARE @animal_json NVARCHAR(MAX)
2> SET @animal_json =
3> N'[
4~ {"no":3,"name":"cow","cry":"mo-!"},{"no":2,"name":"bird","cry":"pi-!"}
5~ ]'
6>
7> insert into animal
8> select *
9> from OPENJSON(@animal_json)
10> WITH (no int' strict $.no',
11> name varchar(10) 'strict $.name',
12> cry varchar(10) 'strict $.cry')★テーブル形式に変換してからINSERTします
13>
14> select * from animal
15> go

(2 行処理されました)
no name cry
---- ------------------------------ ----------
1 dog wan
2 cat nya!
3 cow mo-!
2 bird pi-!
★INSERTされていることを確認できました。

(4 行処理されました)
1>

無事、JSON形式のデータの登録と結果の確認ができました。

今回は「JSONサポート」機能を色々と試してみました。初期設定も不要で使いやすいです!
またStandardエディションでも使用が可能なのでぜひ試してみてください!

Standby Expressに関するお問合わせ

  • TEL 042-333-6217
  • FAX 042-352-6101
  • LINE
  • Mail