Access data from JSON_TABLE with SQL in HANA

DirkO — Mon, 12 Mar 2018 20:45:59 GMT

Maybe u come across a subject like this as well.

Transfer data from a big JSON to a relational table in HANA and second consume the stored information with SQL..

1. Create a table in HANA to store the JSON information.

2. Extract the data with SQL and to a result table.

Here is my solution:

Given a JSON file below I want to access information by using some SQL in HANA.

{
    "metadata": {
        "language": "de-DE",
        "transaction_id": "1511335024960:1650367502",
        "version": "1",
        "latitude": 37.54,
        "longitude": 5.58,
        "units": "m",
        "expire_time_gmt": 1511335872,
        "status_code": 200
    },
    "forecasts": [
        {
            "class": "fod_long_range_daily",
            "expire_time_gmt": 1511335872,
            "fcst_valid": 1511330400,
        
            "night": {
                "fcst_valid": 1511373600,
                "fcst_valid_local": "2017-11-22T19:00:00+0100",
                "day_ind" : "N"
            },

            "day": {
                "fcst_valid": 1511330400,
                "fcst_valid_local": "2017-11-22T07:00:00+0100",
                "day_ind" : "D"
            }
        }
    ]
}
');

I created a table "CREATE TABLE <SCHEMA>.FoD (A INT, B CLOB);" in HANA and dumped the JSON to it.

The nested information can be accessed by using this SQL:

-- select from an ordinality column with nested columns
SELECT *
FROM JSON_TABLE (
        <SCHEMA>.FoD.B, '$'
        
        COLUMNS (
            RN FOR ORDINALITY,
            language_code NVARCHAR(50) PATH '$.metadata.language',
            transaction_id NVARCHAR(50) PATH '$.metadata.transaction_id',
            version NVARCHAR(50) PATH '$.metadata.version',            
            latitude NVARCHAR(50) PATH '$.metadata.latitude',            
            longitude NVARCHAR(50) PATH '$.metadata.longitude',            
            units NVARCHAR(50) PATH '$.metadata.units',            
            expire_time_gmt NVARCHAR(50) PATH '$.metadata.expire_time_gmt',    
            status_code NVARCHAR(50) PATH '$.metadata.status_code',
            
-- Extract the information from section "forecasts"    
        
            NESTED PATH '$.forecasts[*]'
            COLUMNS (
                class NVARCHAR(50) PATH '$.class',
                
-- Extract the information from section "night" as part of section "forecasts"                        
                    NESTED PATH '$.night'
                        COLUMNS (
                            fcst_valid_local_night NVARCHAR(24) PATH '$.fcst_valid_local',
                            night_ind NVARCHAR(1) PATH '$.day_ind' ) 
                            
                            ,
-- Extract the information from section "day" as part of section "forecasts"            
                    NESTED PATH '$.day'
                        COLUMNS (
                            fcst_valid_local_day NVARCHAR(24) PATH '$.fcst_valid_local',
                            day_ind NVARCHAR(1) PATH '$.day_ind' )        
                                                        
            )
        )
) AS JT;

To avoid multiple columns for "fcst_valid_local" with the extension "_day" and "_night" from above I used to combine extractions from the "day" and "night" section of "forecast" by using UNION.

SELECT
     * 
FROM ( 

    SELECT
     * 
    FROM JSON_TABLE ( <SCHEMA>.FoD.B,
     '$' COLUMNS ( RN FOR ORDINALITY,
     language_code NVARCHAR(50) PATH '$.metadata.language',
     transaction_id NVARCHAR(50) PATH '$.metadata.transaction_id',
     version NVARCHAR(50) PATH '$.metadata.version',
     latitude NVARCHAR(50) PATH '$.metadata.latitude',
     longitude NVARCHAR(50) PATH '$.metadata.longitude',
     units NVARCHAR(50) PATH '$.metadata.units',
     expire_time_gmt NVARCHAR(50) PATH '$.metadata.expire_time_gmt',
     status_code NVARCHAR(50) PATH '$.metadata.status_code',
     -- Extract the information from section "forecasts"    
 NESTED PATH '$.forecasts[*]' COLUMNS ( class NVARCHAR(50) PATH '$.class',
     -- Extract the information from section "night" as part of section "forecasts"                        
 NESTED PATH '$.night' COLUMNS ( fcst_valid_local NVARCHAR(24) PATH '$.fcst_valid_local',
     ind NVARCHAR(1) PATH '$.day_ind' ) ) ) ) AS JT_night 
    
    UNION 
    
    SELECT
     * 
    FROM JSON_TABLE ( <SCHEMA>.FoD.B,
     '$' COLUMNS ( RN FOR ORDINALITY,
     language_code NVARCHAR(50) PATH '$.metadata.language',
     transaction_id NVARCHAR(50) PATH '$.metadata.transaction_id',
     version NVARCHAR(50) PATH '$.metadata.version',
     latitude NVARCHAR(50) PATH '$.metadata.latitude',
     longitude NVARCHAR(50) PATH '$.metadata.longitude',
     units NVARCHAR(50) PATH '$.metadata.units',
     expire_time_gmt NVARCHAR(50) PATH '$.metadata.expire_time_gmt',
     status_code NVARCHAR(50) PATH '$.metadata.status_code',
     -- Extract the information from section "forecasts"    
 NESTED PATH '$.forecasts[*]' COLUMNS ( class NVARCHAR(50) PATH '$.class',
     -- Extract the information from section "day" as part of section "forecasts"            
 NESTED PATH '$.day' COLUMNS ( fcst_valid_local NVARCHAR(24) PATH '$.fcst_valid_local',
     ind NVARCHAR(1) PATH '$.day_ind' ) ) ) ) AS JT_day )

Any idea how this can be achieved in an more elegant way?

Regards

Dio.

Re: Access data from JSON_TABLE with SQL in HANA

pfefferf — Tue, 13 Mar 2018 06:10:01 GMT

Please can you show how you access the "forecasts" array within your JSON using the JSON_TABLE function, to be able to check what you make possibly wrong.

Question Re: Access data from JSON_TABLE with SQL in HANA in Technology Q&A

Access data from JSON_TABLE with SQL in HANA

Re: Access data from JSON_TABLE with SQL in HANA