JSON_TABLE Function

SAP HANA has several functions to help query JSON data and in this article, we'll take a look at how to use the JSON_TABLE function to query JSON data stored in a regular column.

Let's start by creating an employee table with a single NVARCHAR column that will contain JSON data for a single employee and populate it with some dummy data.

Listing 1

CREATE ROW TABLE EMPLOYEE (DATA NVARCHAR(3000));

Listing 2

INSERT INTO EMPLOYEE (DATA) VALUES('{ 
    "Name": "John",
    "Salary": 60000,
    "Address": {
        "Street": "100 Riverdale Ave",
        "City": "San Diego",
        "State": "CA"

    }
}');

INSERT INTO EMPLOYEE (DATA) VALUES('{ 
    "Name": "Suzan",
    "Salary": 70000,
    "Address": {
        "Street": "200 Sunnydale Ave",
        "City": "Miami",
        "State": "FL"

    }
}');

INSERT INTO EMPLOYEE (DATA) VALUES('{ 
    "Name": "Ali",
    "Salary": 56000,
    "Address": {
        "Street": "300 Bushey Mill Lane",
        "City": "San Antonio",
        "State": "TX"

    }
}');

JSON_TABLE Function


Now that we have a few employee records, let's take a look at how we can query the data. The JSON_TABLE function creates a relational table from a JSON object. To do this, the function needs several arguments. First, the function needs to know about the JSON data. This data can come from a string literal or a table column. The second argument is a SQL/JSON expression that indicates which JSON elements to use when producing the output. The third argument is a column definition that essentially maps object properties to column names.

Let's take a look at a few examples. Remember that the JSON_TABLE function creates a relational table, this means you can treat it like a regular table.

Listing 3

SELECT * FROM 

JSON_TABLE(EMPLOYEE.DATA , '$'
    COLUMNS(
        NAME VARCHAR(20) PATH '$.Name'
    )
)

In the code sample above the JSON_TABLEfunction is provided with the JSON data that is stored in the DATA column of the EMPLOYEE table. We provide it with the $ JSON expression to indicate we want to use the whole object as the source and finally we use the columns definition to specify that we want the Name property of the JSON data to be mapped to a NAME column of type VARCHAR(20).

Executing the query above will produce three rows of each employee's name. We can add the employee's salary to the result by modifying the columns definition as show below.

Listing 4

SELECT * FROM 

JSON_TABLE(WEB_WMS.EMPLOYEE.DATA , '$'
    COLUMNS(
        NAME VARCHAR(20) PATH '$.Name',
        SALARY DOUBLE PATH '$.Salary'
    )
)

HANA DB

Rust

Java

SAP Business One

Node.js