たまたま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」という定型作業しか経験のない人材を押し込んでくることになりかねないので要注意です。