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.
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.
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.
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 Articles
-
Connect To HANA DB Using Rust
Learn how to connect and query a HANA database using Rust with the hdbconnect package.
21 February 2023 - 2686 views -
If Table Exists Function
In this article, I explain how to create a function to determine if a table exists in SAP HANA.
31 October 2019 - 8718 views -
Connect To HANA DB Using NodeJs
This article explains how to connect to a SAP HANA system using the Node HANA client library.
28 October 2019 - 7072 views -
Connect To HANA DB Using Python
This article explains how to connect to a SAP HANA system using the Python hdbcli package.
11 October 2018 - 7881 views -
Adding Auto Numbers To Table Columns
This article explains how to create an auto generated column using IDENTITY.
14 March 2018 - 6125 views -
Connecting To SAP HANA Using PHP ODBC
This article describes how to install and configure PHP odbc to connect to a SAP HANA system on Ubuntu 16.
01 December 2017 - 7584 views -
Connect To HANA DB Using Java
This article describes how to connect to a HANA DB system using the ngdbc.jar Java driver.
03 November 2017 - 5302 views