Syntax issue while converting JSON to table SAP HANA

[former_member327542]

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

[eralper_yilmaz]

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

[DirkO]

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.