Example JSON statements for connection points using SQL Server Database

Table setup

Create the order table:

CREATE TABLE dbo.ORDER (
        OrderId      INT            NOT NULL PRIMARY KEY,
        OrderType    INT            NOT NULL,
        CustomerName NVARCHAR(200)  NOT NULL,
        Quantity     INT            NOT NULL,
        Item         INT            NOT NULL,
        ItemSize     NVARCHAR(10)   NOT NULL,
        Total        DECIMAL(10,2)  NOT NULL,
        OrderDate    DATETIME2      NOT NULL
        );

Create the JSON input log table:

CREATE TABLE dbo.JSON_INPUT_LOG (
        LogId        INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
        ReceivedJson NVARCHAR(MAX)  NOT NULL,
        ReceivedAt   DATETIME2      NOT NULL DEFAULT GETDATE(),
        Status       NVARCHAR(50)   NOT NULL DEFAULT 'RECEIVED'
        );

Stored procedures

Use this stored procedure to read orders as JSON:

CREATE PROCEDURE [dbo].[Read_Order_JSON_Auto]
        AS
        BEGIN
        SET NOCOUNT ON;
        DECLARE @json NVARCHAR(MAX);
        
        SELECT @json = (
        SELECT TOP 5
        OrderId,
        OrderType,
        CustomerName,
        Quantity,
        Item,
        ItemSize,
        Total,
        OrderDate
        FROM dbo.ORDER
        FOR JSON AUTO
        );
        
        SELECT CAST(@json AS VARCHAR(MAX)) AS JsonResult;
        END;

Use this stored procedure to insert orders from JSON. ION passes a JSON payload to the procedure, which validates, parses, and inserts the order data:

CREATE PROCEDURE [dbo].[ReceiveOrder_JSON_SP]
        @InputJson NVARCHAR(MAX)
        AS
        BEGIN
        SET NOCOUNT ON;
        
        -- Validate that input is valid JSON
        IF ISJSON(@InputJson) = 0
        BEGIN
        RAISERROR('Invalid JSON input provided to ReceiveOrder_JSON_SP.', 16, 1);
        RETURN;
        END
        
        -- Parse and insert the JSON array into the pizza order table
        INSERT INTO dbo.ORDER
        (OrderId, OrderType, CustomerName, Quantity, Item, ItemSize, Total, OrderDate)
        SELECT
        JSON_VALUE(value, '$.OrderId')       AS OrderId,
        JSON_VALUE(value, '$.OrderType')     AS OrderType,
        JSON_VALUE(value, '$.CustomerName')  AS CustomerName,
        JSON_VALUE(value, '$.Quantity')      AS Quantity,
        JSON_VALUE(value, '$.Item')          AS Item,
        JSON_VALUE(value, '$.ItemSize')      AS ItemSize,
        JSON_VALUE(value, '$.Total')         AS Total,
        JSON_VALUE(value, '$.OrderDate')     AS OrderDate
        FROM OPENJSON(@InputJson, '$.DataArea');
        END;

Use this stored procedure to store the unmodified JSON payload for traceability or troubleshooting:

CREATE PROCEDURE [dbo].[Write_LogRawJSON]
        @InputJson NVARCHAR(MAX)
        AS
        BEGIN
        SET NOCOUNT ON;
        
        INSERT INTO dbo.JSON_INPUT_LOG
        (ReceivedJson, ReceivedAt, Status)
        VALUES
        (@InputJson, GETDATE(), 'RECEIVED');
        END;

Execution commands

Use this command to read orders:

EXEC dbo.Read_Order_JSON_Auto;

Use this command to insert orders from JSON:

EXEC dbo.ReceiveOrder_JSON_SP @InputJson = [Data];

Use this command to log a raw JSON payload:

EXEC dbo.Write_LogRawJSON @InputJson = ?;