Problem with HANA JSON_TABLE() function

[iperez-sofos]

Greetings.

I am using the JSON_TABLE() function inside a stored procedure that I am writing.

The JSON data has a non-trivial structure, below I leave the expression that I am using exactly.

 -- "flatten" transaction data
 lt_flatTxnData = SELECT JT.idxNameID, JT.idxName, JT.idxDescr, JT.idxClass, JT.idxMeasUnit,
                         JT.idxStage, JT.maxStdDev, JT.idxRelative, JT.refNameID, JT.breed,
                         JT.gender, JT.season, JT.refBasVal, JT.refN, JT.refDayNum, JT.refFac,
                         JT.refFacAcum, JT.refStdDev, JT.histNameID, JT.housingUn, JT.histBasVal,
                         JT.histN, JT.histDayNum, JT.histFac, JT.histFacAcum, JT.histStdDev
                    FROM JSON_TABLE(:txnData, '$'
                             COLUMNS
                             (
                                 idxNameID NVARCHAR(111) PATH '$.nameID',
                                 idxName NVARCHAR(111) PATH '$.name',
                                 idxDescr NVARCHAR(1111) PATH '$.descr',
                                 idxClass NVARCHAR(111) PATH '$.idxClass_item',
                                 idxMeasUnit NVARCHAR(111) PATH '$.measUnit_ID',
                                 idxStage NVARCHAR(36) PATH '$.stage_ID',
                                 maxStdDev DOUBLE PATH '$.maxStdDev',
                                 idxRelative NVARCHAR(10) PATH '$.isRelative',
                                 NESTED PATH '$.curves'
                                 COLUMNS
                                 (
                                     refNameID NVARCHAR(111) PATH '$.nameID',
                                     breed NVARCHAR(36) PATH '$.breed_ID',
                                     gender NVARCHAR(111) PATH '$.gender_item',
                                     season NVARCHAR(36) PATH '$.season_ID',
                                     refBasVal DOUBLE PATH '$.baseValue',
                                     refN INTEGER PATH '$.n',
                                     NESTED PATH '$.dataPoints'
                                     COLUMNS
                                     (
                                         refDayNum INTEGER PATH '$.dayNum',
                                         refFac DOUBLE PATH '$.f',
                                         refFacAcum DOUBLE PATH '$.fAcum',
                                         refStdDev DOUBLE PATH '$.stdDev'
                                     ),
                                     NESTED PATH '$.historicCurves'
                                     COLUMNS
                                     (
                                         histNameID NVARCHAR(111) PATH '$.nameID',
                                         housingUn NVARCHAR(36) PATH '$.housingUnit_unit_ID',
                                         histBasVal DOUBLE PATH '$.baseValue',
                                         histN INTEGER PATH '$.n',
                                         NESTED PATH '$.dataPoints'
                                         COLUMNS
                                         (
                                             histDayNum INTEGER PATH '$.dayNum',
                                             histFac DOUBLE PATH '$.f',
                                             histFacAcum DOUBLE PATH '$.fAcum',
                                             histStdDev DOUBLE PATH '$.stdDev'
                                         )
                                     )
                                 )
                             )
                         ) AS JT; 

My goal (my initial intention) is to put all the JSON dataset into a temporary table (many people call this "flattening" the data), and then, using the temporary table, extract three different pieces of the data to insert into three different tables; one table for the top-level columns, another table for the '$ .curves' section, and another table for the '$.historicCurves' section.

Something that makes the requirement more complicated is that the '$ .curves' and '$ .historicCurves' sections are optional, so these may or may not be in the JSON data.

Regarding this last requirement comes my main question:

Is it possible to do what I am looking for or does the JSON_TABLE function not allow it (that is, optional sections)? (I have already checked the documentation for the JSON_TABLE function in https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/f8f6916b5c434a6fbd1bb7e0dc..., but I don't know if I have misunderstood something.).

The code I put in earlier has no compilation errors, but at the moment it generates a run-time error when the optional sections are missing:

"error": {

"code": 339,

"message": "invalid number: <ErrorPath>: invalid nested path expression"

}

With that error message, I wonder if something is missing in my JSON_TABLE expression (or if something is wrong)

Thank you very much in advance for any help and/or suggestions.

[pfefferf]

Do you have an example of such a JSON which you can share.

[iperez-sofos]

For sure yes, florian.pfeffer.

I am attaching an example JSON that I am using to test the stored procedure that I am writing (maybe you can notice that the two sections '$ .dataPoints' have the same set of values; that is just for ease for me when building this example, in real scenarios it doesn't have to be like this).

json-example.txt

[pfefferf]

What HANA version you are using?

And what type of kind has variable txnData?

I tested it on HANA Cloud with a NVARCHAR typed variable and got a result (without the error you described).

[iperez-sofos]

I am using an instance of the SAP HANA Cloud service, in my SAP BTP Trial account.

The txnData variable is of type NCLOB (to be able to support large JSONs).

[pfefferf]

I do not get an error with nclob type too. Compared to your code if have not made an adjustment in the statement which uses JSON_TABLE. Just txnData is declared with type nclob and the json example you provided (I removed all not necessary flags, new lines and carriage returns in the JSON data).

[seitz_alex]

Hello Isaac,

have you ever fixed the problem?

was it because of the optional entries in '$ .curves' and '$ .historicCurves'?

best regards,

Alex