[筆記]MSSQL JSON解析

OPENJSON 函數會接受單一 JSON 物件或 JSON 物件的集合，並將其轉換成一或多個資料列。 根據預設，OPENJSON 函式會傳回下列資料：
從 JSON 物件，此函式會傳回可在第一層找到的所有索引鍵/值組。
從 JSON 陣列，此函式會傳回所有陣列元素及其索引。

當您使用 OPENJSON 函式而不提供明確的結果結構描述 (也就是在 OPENJSON 之後不使用 WITH 子句) 時，此函式會傳回包含下列三個資料行的資料表：
輸入物件中的屬性名稱 (或輸入陣列中元素的索引)。
屬性或陣列元素的值。
類型 (例如字串、數字、布林值、陣列或物件)。

DECLARE @members NVARCHAR(MAX) = '[{"name": "王大山", "sex": "male"},{"name": "李碩德", "sex": "male"}]'
 
SELECT * FROM OPENJSON(@members) WITH (name NVARCHAR(64),sex NVARCHAR(64)) as data

當您使用 OPENJSON 函數的 WITH 子句指定結果的結構描述時，此函數會傳回只包含您在 WITH 子句中所定義之資料行的資料表。 在選擇性 WITH 子句中，您可以指定一組輸出資料行、其類型，以及每個輸出值的 JSON 來源屬性路徑。 OPENJSON 會逐一查看 JSON 物件的陣列、讀取為每個資料行指定之路徑上的值，並將值轉換成指定的型。

DECLARE @data NVARCHAR(MAX) = {{@N7173560739599355904}};
DECLARE @index INT = 0;
DECLARE @ITEM_NUMBER NVARCHAR(64), @ITEM_DESCRIPTION NVARCHAR(64), @QUANTITY NVARCHAR(64), @UNIT_PRICE NVARCHAR(64), @NEED_BY_DATE NVARCHAR(64), @LINE_ID NVARCHAR(64), @DOCUMENT_LINE_NUM NVARCHAR(64), @CHANGE_FLAG NVARCHAR(64), @LAST_UNIT_PRICE NVARCHAR(64);

WHILE @index < (SELECT COUNT(*) FROM OPENJSON(@data))
BEGIN
    SELECT @ITEM_NUMBER = JSON_VALUE(value, '$.ITEM_NUMBER'),
           @ITEM_DESCRIPTION = JSON_VALUE(value, '$.ITEM_DESCRIPTION'),
           @QUANTITY = JSON_VALUE(value, '$.QUANTITY'),
           @UNIT_PRICE = JSON_VALUE(value, '$.UNIT_PRICE'),
           @NEED_BY_DATE = JSON_VALUE(value, '$.NEED_BY_DATE'),
           @LINE_ID = JSON_VALUE(value, '$.LINE_ID'),
           @DOCUMENT_LINE_NUM = JSON_VALUE(value, '$.DOCUMENT_LINE_NUM'),
           @CHANGE_FLAG = JSON_VALUE(value, '$.CHANGE_FLAG'),
           @LAST_UNIT_PRICE = JSON_VALUE(value, '$.LAST_UNIT_PRICE')
    FROM OPENJSON(@data)
    WHERE [key] = @index;

    -- 在這裡使用 INSERT INTO 語句將資料插入目標表格
    INSERT INTO FM7T_ORACLEPO_D(RequisitionID, ITEM_NUMBER, ITEM_DESCRIPTION, QUANTITY, UNIT_PRICE, NEED_BY_DATE, LINE_ID, DOCUMENT_LINE_NUM, CHANGE_FLAG, LAST_UNIT_PRICE)
    VALUES({{@N7173144044422434816.0.requisitionID}}, @ITEM_NUMBER, @ITEM_DESCRIPTION, @QUANTITY, @UNIT_PRICE, @NEED_BY_DATE, @LINE_ID, @DOCUMENT_LINE_NUM, @CHANGE_FLAG, @LAST_UNIT_PRICE);

    SET @index = @index + 1;
END;