Skip to Content

Syntax issue while converting JSON to table SAP HANA

Hi,

I have created JSON table as below.

CREATE 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}
        ]
}
');

I wanted to convert Line item JSON to table and using below query to do it.

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;

But I am getting syntax error as below

Could not execute 'SELECT JT.* FROM JSON_TABLE ( DEVTEST.T1.B, '$.LineItems[*]' COLUMNS ( RN FOR ORDINALITY, ...'
Error: (dberror) 257 - sql syntax error: incorrect syntax near "COLUMNS": line 45 col 2 (at pos 66)

Could anyone help me.

Regards,

Viswateja

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 12 at 07:32 PM

    Namaste Viswateja

    don´t u also need to use a DB-schema, or did u set it with schema = DEVTEST; beforehand?

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

    Regards

    Dio.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 13 at 05:20 AM

    Did you enable JSON Document Store on your HANA database platform?

    Add comment
    10|10000 characters needed characters exceeded