たまたまAzureのSQL DATABASE(≒SQL Server)のドキュメントを読んでいたときに気が付いたのですが、最近のSQL Serverってクエリ末尾に”FOR JSON”を付与すればクエリ結果をそのままJSONに出来るんですって！？そんなこと知らずにDapperでガリガリとSQLをパースしてJSONを作っていたとか馬鹿丸出しですねワタシ。パトラッシュ・・次から頑張るよ・・・・。
ということでSQLだけで多層ネストJSONが出来るまでを検証しました。以下にその方法を示します。
準備：Azure上にサンプルDBを作成 (テンプレートの”AdventureWorkLT”を利用)
このサンプルデータを利用して「セールスの存在する顧客別にオーダーとそのオーダー詳細を表示する」という3層ネスト構造を再現します。
SQLで表現するとこんな感じです。
SELECT CUST.CustomerID,
CUST.FirstName,
CUST.LastName,
CUST.Phone,
SHDR.SalesOrderID,
SHDR.OrderDate,
SHDR.SubTotal,
DETL.SalesOrderDetailID,
DETL.ProductID,
PRDT.Name,
DETL.LineTotal
FROM SalesLT.Customer CUST
INNER JOIN SalesLT.SalesOrderHeader SHDR
ON SHDR.CustomerID = CUST.CustomerID
INNER JOIN SalesLT.SalesOrderDetail DETL
ON DETL.SalesOrderID = SHDR.SalesOrderID
INNER JOIN SalesLT.Product PRDT
ON PRDT.ProductID = DETL.ProductID
ORDER BY CUST.CustomerID,SHDR.SalesOrderID,DETL.SalesOrderDetailID
実行するとこんな感じ。
いかにもDapperで分割してくださいと言わんばかりの出力結果ですが、今回はDapperは使わずSQL DATABASEの基本機能だけしか使いません。ですのでこのSQLの文末に”FOR JSON AUTO”を付与すればめでたく解決・・・ではなく、ここからネストさせたい階層ごとにサブクエリを定義していく必要があります。それをストアドファンクション化したのが以下となります。
CREATE OR ALTER FUNCTION GetSalesInfoByValidCustomer()
RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @json nvarchar(MAX)
SET @json = (
SELECT CUST.CustomerID,
CUST.FirstName,
CUST.LastName,
CUST.Phone,
(
SELECT
SHDR.SalesOrderID,
SHDR.OrderDate,
SHDR.SubTotal
,(
SELECT
DETL.SalesOrderDetailID,
DETL.ProductID,
PRDT.Name,
DETL.LineTotal
FROM SalesLT.SalesOrderDetail DETL
INNER JOIN SalesLT.Product PRDT
ON PRDT.ProductID = DETL.ProductID
WHERE DETL.SalesOrderID = SHDR.SalesOrderID
ORDER BY DETL.SalesOrderDetailID
FOR JSON PATH
) AS DETAIL
FROM SalesLT.SalesOrderHeader SHDR
WHERE SHDR.CustomerID = CUST.CustomerID
ORDER BY SHDR.SalesOrderID
FOR JSON PATH
) AS [ORDERS]
FROM SalesLT.Customer CUST
INNER JOIN SalesLT.SalesOrderHeader SHDR
ON SHDR.CustomerID = CUST.CustomerID
ORDER BY CUST.CustomerID
FOR JSON PATH,ROOT('INFO')
)
RETURN @json;
END
ポイントは次の通りです。
- JSONの受けは必ずNVARCHAR(MAX)とする
- “FOR JSON PATH”とする(PATH付けないとコケる)
- ROOT(‘INFO’)とすることでルートオブジェクト配列にアクセスしやすくする
- サブクエリでのエイリアスがJSONでの型名となる
こうして作成したJSONをオンラインパーサーで確認するとちゃんと3層ネスト構造となっていました。
あとはこのまま普通にHTTP TriggerやPHPファイルからレスポンスを返してあげればREST APIの作成完了なのです。余談ですが逆にSQLだけでJSONを解析することも可能です。
--JSON⇒テーブル化用クエリ DECLARE @json nvarchar(MAX) SET @json= dbo.GetSalesInfoByValidCustomer(); SELECT Level2.CustomerID, Level2.FirstName, Level2.LastName, Level2.Phone, Level2.SalesOrderID, Level2.OrderDate, Level2.SubTotal, SalesOrderDetailID, ProductID, Name , LineTotal FROM ( SELECT Level1.CustomerID, Level1.FirstName, Level1.LastName, Level1.Phone, SalesOrderID, OrderDate, SubTotal, DETAIL FROM ( SELECT CustomerID, FirstName, LastName, Phone, ORDERS FROM OPENJSON(@json,N'strict $.INFO') WITH ( CustomerID int, FirstName nvarchar(50), LastName nvarchar(50), Phone nvarchar(25), ORDERS nvarchar(MAX) AS JSON ) ) Level1 CROSS APPLY OPENJSON(Level1.ORDERS) WITH( SalesOrderID int, OrderDate datetime, SubTotal money, DETAIL nvarchar(MAX) AS JSON ) ) Level2 CROSS APPLY OPENJSON(Level2.DETAIL) WITH( SalesOrderDetailID int, ProductID int, Name nvarchar(50), LineTotal numeric(38,6) ) ;
実はこの逆変換の方が大変でした。ポイントは次の通りです。
- “CROSS APPLY OPENJSON”を用いてJSON型を手動で展開する
- 展開したJSONの中に再度JSON型があった場合はさらに展開する
- OPENJSONのWITHにて型指定する必要がある(型推論ないんかい・・・
- ”CROSS APPLY”で展開した列名は展開対象のエイリアスとは別となる
- “CROSS APPLY”しても展開元のエイリアスは呼べる(これになかなか気付かなかった・・・)
今どきのSQLServerってこんな感じでSQLを駆使していけばJSON化もパースもできるんですね。でも楽になった気があまりしないのは気のせいではないかもしれません。正直、慣れてなければ面倒くさいからこれならDapper使うわってのもアリではないでしょうか。あとSQLの見通しが悪くなってるのがかなり保守観点でマイナス。
ですので間違ってもこれらの「SQLだけでREST APIは作れる」という知識だけで 判断してはダメです。
「(言語名)経験○○年の要員がホゲホゲ」なんて考えのマトモにスキル判定も出来ない無能揃いのSES業者が「運用でSQLバリバリやってましたHAHAHA」という定型作業しか経験のない人材を押し込んでくることになりかねないので要注意です。
