Json處理

CREATE PROCEDURE xtp.ProductList (@ProductIds NVARCHAR(100))
WITH SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN
    ATOMIC WITH (TRANSACTION ISOLATION LEVEL = snapshot, LANGUAGE = N'English')

    SELECT ProductID,
        Name,
        Price,
        Data,
        Tags,
        JSON_VALUE(data, '$.MadeIn') AS MadeIn
    FROM xtp.Product
    INNER JOIN OPENJSON(@ProductIds)
        ON ProductID = value
END;
GO

DECLARE @json NVARCHAR(MAX)
SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2011-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    }
]'

SELECT * FROM OPENJSON(@json)
WITH (
    Number varchar(200) '$.Order.Number',
    Date datetime '$.Order.Date',
    Customer varchar(200) '$.AccountNumber',
    Quantity int '$.Item.Quantity'
)
DECLARE @json NVARCHAR(MAX)
SET @json = N'{
    "name": "John",
    "surname": "Doe",
    "age": 45,
    "skills": ["SQL", "C#", "MVC"]
}'

SELECT * FROM OPENJSON(@json)

DECLARE @ProductIds NVARCHAR(MAX)
SET @ProductIds = N'[1, 3]'

SELECT ProductID,
       Name,
       Price,
       Data,
       Tags,
       JSON_VALUE(data, '$.MadeIn') AS MadeIn
FROM xtp.Product
INNER JOIN OPENJSON(@ProductIds)
    ON ProductID = value

DROP TABLE IF EXISTS xtp.Product;
GO
CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(400) NOT NULL,
    Price FLOAT,
    Data NVARCHAR(4000),
    MadeIn AS CAST(JSON_VALUE(Data, '$.MadeIn') AS NVARCHAR(50)) PERSISTED,
    Cost AS CAST(JSON_VALUE(Data, '$.ManufacturingCost') AS FLOAT) PERSISTED
)
WITH (MEMORY_OPTIMIZED = ON);
GO