JSON_TABLE Function (JSON)

SAP Help Portal

The following examples use the table created below:

CREATE ROW TABLE T1 (A INT, B NVARCHAR(5000));
INSERT INTO T1 VALUES (1, '
{
        "PONumber": 1,
        "Reference": "BSMITH-74635645",
        "Requestor": "Barb Smith",
        "User": "BSMITH",
        "CostCenter": "A50",
        "ShippingInstructions":
               {
                       "name": "Barb Smith",
                       "Address":
                               {
                                      "street": "100 Fairchild Ave",
                                      "city": "San Diego",
                                       "state": "CA",
                                      "zipCode": 23345,
                                      "country": "USA"
                               },
                       "Phone": [{"type": "Office", "number": "519-555-6310"}]
               },
        "SpecialInstructions": "Surface Mail",
        "LineItems": [
               {"ItemNumber": 1, "Part": {"Description": "Basic Kit", "UnitPrice": 19.95, "UPCCode": 73649587162}, "Quantity": 7},
               {"ItemNumber": 2, "Part": {"Description": "Base Kit 2", "UnitPrice": 29.95, "UPCCode": 83600229374}, "Quantity": 1},
               {"ItemNumber": 3, "Part": {"Description": "Professional", "UnitPrice": 39.95, "UPCCode": 33298003521}, "Quantity": 8},
               {"ItemNumber": 4, "Part": {"Description": "Enterprise", "UnitPrice": 49.95, "UPCCode": 91827739856}, "Quantity": 8},
               {"ItemNumber": 5, "Part": {"Description": "Unlimited", "UnitPrice": 59.95, "UPCCode": 22983303876}, "Quantity": 8}
        ]
}
');

SELECT JT.*
FROM JSON_TABLE(T1.B, '$.LineItems[*]'
COLUMNS
    (
        RN FOR ORDINALITY,
        ITEM_NUMBER INT PATH '$.ItemNumber',
        UPC_CODE BIGINT PATH '$.Part.UPCCode'
    )
) AS JT;

SELECT *
FROM JSON_TABLE(T1.B, '$.ShippingInstructions'
COLUMNS
    (
        PHONE VARCHAR(50) FORMAT JSON PATH '$.Phone'
    )
) AS JT;

SELECT *
FROM JSON_TABLE(T1.B, '$.ShippingInstructions'
COLUMNS
    (
        NESTED PATH '$.Address'
        COLUMNS
        (
            STREET NVARCHAR(50) PATH '$.street',
            CITY NVARCHAR(50) PATH '$.city'
        )
    )
) AS JT;