プログラミング」カテゴリーアーカイブ

AzureのSQL DATABASEから多層ネストされたJSONを返すREST APIを作る場合、SELECT文だけで間に合ってしまう(Transact-SQL)

たまたま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

実行するとこんな感じ。

SQL実行結果

いかにも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層ネスト構造となっていました。

オンラインパーサーでのJSON構造確認結果

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

Azure Cosmos DBにストアドプロシージャからパーティションキーを指定してドキュメントを追加する(C#)

Azure Cosmos DBをSQL(document)で作成すれば普通のSQL文でCRUDできるのではないかと思いましたが、どうやらそうではなく使えるのはSELECT、しかもGROUP BYもできない、トランザクションスコープも適用されないという非常に癖のある代物でした。オールドタイプにはなかなか馴染めないですね。

特にトランザクションスコープは面倒で、ストアドプロシージャを経由すればパーティション単位でトランザクションが保証されるというものです。ちなみにストアドプロシージャの実装方法ははAzureポータル上にてJavaScriptにより定義する必要があります。

仕方がないと諦め、ベタ書きでJavaScriptを書いてストアドプロシージャを作成します。テストデータでは”uniqid”でパーティション化されたコレクションを想定しています。

// SAMPLE STORED PROCEDURE
// ID「InsertDocuments」としてポータル上から登録します
//
// 引数 documents : JSON配列形式の文字列
// 戻値 作成したドキュメントの配列
function InsertDocuments(documents) {
    const collection = getContext().getCollection();
    const collLink = collection.getSelfLink();
    console.log("documents=" + documents +"\n");
    let jsonList;
    try {
        jsonList = JSON.parse(documents);
    } catch(e) {
        console.log(e);
        return null;
    }

    console.log("length=" + jsonList.length +"\n");
    let createdList = [];
    for(let i=0 ; i<jsonList.length ; i++){
        let doc = jsonList[i];

        // Query documents and tolist processed documents.
        let isAccepted = 
            collection.createDocument(
                    collLink, 
                    doc, 
                    function (err, document) {
                if (err) {
                    console.log(err);

                } else {
                    console.log("created " + document.id + "\n");
                    createdList.push(document);
                    if(i==jsonList.length-1){
                        getContext().getResponse().setBody(JSON.stringify(createdList));
                    }
                }
            });
        if (!isAccepted) throw new Error('The query was not accepted by the server.');
    }

    return;
}

次に利用側のコードを書きます。今回、Functions V2(.NET Core)からC#を利用してCosmos DBにアクセスします。

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;

namespace FunctionsTest
{
    public static class FuncInsertDocument
    {
        private static readonly string EndpointUrl = "<your endpoint URL>";
        private static readonly string PrimaryKey = "<your primary key>";  //[Read-only Keys]で問題ない
        private static readonly string DataBaseId = "<your DataBase ID>";
        private static readonly string CollectionId = "<your Collection ID>";

        [FunctionName("FuncInsertDocument")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");
            DocumentClient client = new DocumentClient(new Uri(EndpointUrl), PrimaryKey);

            // 書込データ作成
            var partitionKeyValue = "U001";
            var dataList = new List<TestData>();
            dataList.Add(new TestData{
                UniqId = partitionKeyValue,
                SessionId = "SES001",
                Time = new DateTimeOffset(new DateTime(2018,1,15,0,0,0,DateTimeKind.Local))
            });

            // ストアドプロシージャ"InsertDocuments"を実行する
            Uri spUri = UriFactory.CreateStoredProcedureUri(
                DataBaseId,
                CollectionId,
                "InsertDocuments");
            string spParam = JsonConvert.SerializeObject(dataList);
            RequestOptions options = new RequestOptions() {
                EnableScriptLogging = true,
                PartitionKey = new PartitionKey(partitionKeyValue)
            };
            var result = await client.ExecuteStoredProcedureAsync<string>(
              spUri,
              options,
              spParam);

            // クエリ成功時にはスクリプトログを出力
            ActionResult ret = null;
            if (result.Response != null) {
                log.LogInformation(result.ScriptLog);
                ret = new OkObjectResult($"ret=" + result.ScriptLog);
            } else {
                ret = new BadRequestObjectResult("Please pass a name on the query string or in the request body");
            }
            return ret;
        }
    }

    public class TestData {
        [JsonProperty("uniqid")]
        public string UniqId {get;set;}

        [JsonProperty("sessionid")]
        public string SessionId { get; set; }

        [JsonProperty("time")]
        [JsonConverter(typeof(MyCustomDateTimeConverter))]
        public DateTimeOffset Time { get; set; }
    }

    public class MyCustomDateTimeConverter : IsoDateTimeConverter {
        public MyCustomDateTimeConverter() {
            base.DateTimeFormat = "yyyy-MM-dd'Z'HH:mm:ss'Z'";
        }
    }
}

指定したパーティションキー値と投入データの値が一致しない場合はストアドプロシージャの呼び出しは失敗します。よって実際の利用にあたっては投入前にLINQからパーティションキー単位でグループ化を行い、またコレクションに主キー(Primary Keys)設定を行い、ストアドプロシージャ側に主キー重複時の例外処理などを実装する必要があると思います。

なおストアドプロシージャを経由する追加処理は非常に遅く、あくまでもトランザクションを必要とした少量データの更新などにしか使えません。大量データの取込にはBulkExecuterライブラリを利用して一気に追加/更新をやるのが本来望まれる処理のようです(ただしV1限定)。

Dapperのマルチマッピング機能で3層ネストされたリストを取得する(C#)

お久しぶりです。

今年はWindows Embedded Compact 7によるハンディターミナルの開発ばかりやってたのですが、今月からうってかわってAzure Functions V2によるWebアプリ開発になりました。

振れ幅の大きさに自分でも笑ってしまいます。

それはさておきDBへのクエリ結果から配列がネストされた構造のJSONに変換するのに簡単な方法はないものか色々と悩みましたが、あまり簡単な方法はなかったようでエクセレントな解法ではないものの、まあ許容できる範囲かと思ったので検証結果を公開します。

検証には次のような条件を前提としています。

  • JOINするのは3テーブル
  • それぞれのテーブルには単一カラムの主キーがある
  • クエリ解析にはDapperのマルチマッピング機能を利用する

そして以上の条件を踏まえ、Dapper公式サイトのマルチマッピング機能のソースを改変した検証コードが次です。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using Dapper;

namespace ConsoleApp1 {

    class Program {

        public class MtProducts {
            public int ProductID { get; set; }
            public string ProductName { get; set; }
        }
        public class OrderDetail {
            public int OrderDetailID { get; set; }
            public List ProductList { get; set; }
            public int Quantity { get; set; }
        }

        public class Order {
            public int OrderID { get; set; }
            public int CustomerID { get; set; }
            public int EmployeeID { get; set; }
            public DateTime OrderDate { get; set; }
            public int ShipperID { get; set; }
            public List OrderDetails { get; set; }
        }

        /// 

        /// Dapperマルチマッピングの検証
        /// original: https://dapper-tutorial.net/result-multi-mapping
        /// 
        /// 
        static void Main(string[] args) {
            string sql = @"
                    SELECT TOP 10 
	                A.OrderID,
	                A.CustomerID,
	                A.EmployeeID,
	                A.OrderDate,
	                A.ShipperID,
	                B.OrderDetailID,
	                B.Quantity,
	                MP.ProductID,
	                MP.ProductName
                FROM dbo.Orders AS A 
                INNER JOIN OrderDetails AS B 
                ON A.OrderID = B.OrderID
                INNER JOIN MtProducts AS MP
                ON B.ProductID = MP.ProductID
                          ;";

            SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder() {
                DataSource = @"myserver\SQLSERVER,1433",
                InitialCatalog = "MyDbName",
                UserID = "myuser",
                Password = "mypassword",
                IntegratedSecurity = true

            };

            // 出力用リスト
            var orders = new List();

            // ルックアップテーブル代わりの連想配列
            var ordDict = new Dictionary, List>();
            using (var connection = new SqlConnection(cb.ToString())) {

                // Query(sql,func map,param)
                var list = connection.Query(
                    sql: sql,
                    map: (ord, detail, products) => {
                        // 現在のOrderIDとOrderDetailIDが読込済でなければ初期化する
                        var key = new Tuple(ord.OrderID, detail.OrderDetailID);
                        var pBuf = new List();
                        if (!ordDict.TryGetValue(key, out pBuf)) {
                            detail.ProductList = new List();
                            ord.OrderDetails = new List();
                            ord.OrderDetails.Add(detail);
                            ordDict.Add(key, detail.ProductList);
                            if (orders.Find(x => x.OrderID == ord.OrderID) == null)
                                orders.Add(ord);
                        }

                        // 現在のOrderIDとOrderDetailIDにproductsをリスト追加
                        if (ordDict.TryGetValue(key, out pBuf)) {
                            ordDict[key].Add(products);
                        }
                                
                        // 出力用リストを検索し設定がなければ追加、設定済であれば上書
                        var od1 = orders.First(x => x.OrderID == ord.OrderID);
                        if (od1 != null) {
                            var od2 = orders.Find(x => x.OrderID == ord.OrderID).OrderDetails.Find(y => y.OrderDetailID == detail.OrderDetailID);
                            if (od2 == null) {
                                // 追加
                                detail.ProductList = ordDict[key];
                                orders.Find(x => x.OrderID == ord.OrderID).OrderDetails.Add(detail);
                            } else {
                                // 上書
                                detail.ProductList = ordDict[key];
                                orders.Find(x => x.OrderID == ord.OrderID).OrderDetails.Find(y => y.OrderDetailID == detail.OrderDetailID).ProductList = detail.ProductList;
                            }
                        }

                        // この戻り値はダミー
                        return ord;
                    },
                    commandType: null,
                    splitOn: "OrderDetailID,ProductID"
                );

                // 出力用リストを返す
                var s = JsonConvert.SerializeObject(orders);
                Console.Write(s);
            }

        }
    }
}
-- DDLなど
CREATE TABLE [dbo].[Orders](
	[OrderID] [int] NOT NULL,
	[CustomerID] [int] NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[OrderDate] [datetime] NOT NULL,
	[ShipperID] [int] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderDetails](
	[OrderDetailID] [int] NOT NULL,
	[OrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
(
	[OrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MtProducts](
	[ProductID] [int] NOT NULL,
	[ProductName] [ntext] NULL,
 CONSTRAINT [PK_MtProducts] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO [dbo].[MtProducts]([ProductID],[ProductName]) VALUES (1,'肉');
INSERT INTO [dbo].[MtProducts]([ProductID],[ProductName]) VALUES (2,'魚');
INSERT INTO [dbo].[MtProducts]([ProductID],[ProductName]) VALUES (3,'米');
GO
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10248,90,5,'2018/07/04 12:00:00',3);
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10249,81,6,'2018/07/05 12:00:00',1);
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10250,34,4,'2018/07/06 12:00:00',2);
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10251,84,3,'2018/07/07 12:00:00',1);
GO
INSERT INTO [dbo].[OrderDetails]([OrderDetailID],[OrderID],[ProductID],[Quantity]) VALUES (1,10248,1,12);
INSERT INTO [dbo].[OrderDetails]([OrderDetailID],[OrderID],[ProductID],[Quantity]) VALUES (2,10248,2,10);
INSERT INTO [dbo].[OrderDetails]([OrderDetailID],[OrderID],[ProductID],[Quantity]) VALUES (3,10249,3,5);

上記のコードのポイントは次の通りです。

  • レコードの読込判定にディクショナリを用いる
  • ディクショナリに格納するのは最下層の配列
  • 最終的に出力するリストにひとまず追加し、追加済みであれば上書する

じゃあ3階層を超えたネストはどうすればいいんだ、という疑問については

「3階層目からメソッド分割して別途Query呼び出せばいいんじゃね?」

と思ったりしますが、DapperのQueryがネストして呼び出せるかどうか試していないので断言は出来ません。

それにしてもWordpressを更新したら、前のような記述が出来なくなって、やたら平坦て簡素な書き方を強いられてしまいます。

しかもシンタックスハイライターが動かんのでソースコード見づらいし・・・。

勝手に変えるなよ・・・と思っても、何も考えずよく知らないWordpressを選んだ私の自業自得なのでしょう。

汎用CSV読込フォーム

ブログでは初めてのプログラミング関係の記事です。
CsvReader動作イメージ

下記に汎用CSV読込フォームに関する記事を公開しました。
汎用CSV読込フォーム CsvReaderについて

使い方とかソースコードの説明とか省略しまくったので、もし質問があればコメントをください。
もちろん指摘やバグ報告も歓迎します。

まあ、すぐに対応できるとも対応するとも限りませんがね:-p)