Extract JSON Data via SQL Functions in Teradata

access_time 22 days ago visibility16 comment 0

JSON data type is supported in Teradata from version 15.10. Together with native JSON type, a number of JSON functions are added to support extracting values from JSON, shredding JSON, etc. 

infoThe following code snippets use string literal to demonstrate the usage of these functions; you can replace them with any JSON column in your table.

JSONExtractValue function

JSONExtractValue is used to extract single value using JSONPath. 

Example:

SElECT NEW JSON('{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}').JSONExtractValue('$.menu.id');

Output:

file

*JSON example is cited from https://json.org/example.html.

Example:

SElECT NEW JSON('{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}').JSONExtractValue('$.menu.value');

Output:

File

Example:

SElECT NEW JSON('{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}').JSONExtractValue('$.menu.popup.menuitem[1].value');

Result:

Open

JSONExtract function

JSONExtract can return a JSON array. 

Example:

SElECT NEW JSON('{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}').JSONExtract('$.menu.popup.menuitem');

Output:

[[{"value":"New","onclick":"CreateNewDoc()"},{"value":"Open","onclick":"OpenDoc()"},{"value":"Close","onclick":"CloseDoc()"}]]

The output is a JSON array.

Example:

SElECT NEW JSON('{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}').JSONExtract('$.menu.popup.menuitem.*.value');

Output:

["New","Open","Close"]

JSON shredding via JSON_TABLE function

JSON_TABLE function create a temporary table based on all or a subset of JSON object.

Example:

SELECT * FROM JSON_Table 
(ON (SELECT 0 as id, NEW JSON('{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}') as jsonCol )
USING rowexpr('$.menu.popup.menuitem[*]')
               colexpr('[ {"jsonpath" : "$.value",
                           "type" : "VARCHAR(5)"},
                          {"jsonpath" : "$.onclick",
                           "type" : "VARCHAR(20)"}]')
) AS JT(id, "Value", "OnClick");

Output:

	id	Value	OnClick
1 0 New CreateNewDoc()
2 0 Open OpenDoc()
3 0 Close CloseDoc()
There are several things to pay attention to:
  • There are two columns for JSON_TABLE: id - uniquely identify row ID in your underlying table; jsonCol - a column with JSON type.
  • JSONPath is used to retrieve values for each column.

Convert table to JSON object via JSON_AGG

JSON_AGG function can be used to convert a table to JSON object.

Let's create a simple table using these code snippets:

create set table TestDb.test_table
(
id int not null,
category varchar(10),
amount int
)
primary index (id);

insert into TestDb.test_table values(1,'A',10);
insert into TestDb.test_table values(2,'A',11);
insert into TestDb.test_table values(3,'A',12);
insert into TestDb.test_table values(4,'B',100);
insert into TestDb.test_table values(5,'B',101);
insert into TestDb.test_table values(6,'B',102);
insert into TestDb.test_table values(4,'C',1000);
insert into TestDb.test_table values(5,'C',1001);
insert into TestDb.test_table values(6,'C',1002);

Now we can use JSON_AGG to convert the table to JSON array:

SELECT JSON_AGG(id, category, amount) 
FROM TestDb.test_table;

Output:

[{"id":5,"category":"B","amount":"101."},{"id":5,"category":"C","amount":"1001."},{"id":3,"category":"A","amount":"12."},{"id":1,"category":"A","amount":"10."},{"id":6,"category":"B","amount":"102."},{"id":6,"category":"C","amount":"1002."},{"id":4,"category":"B","amount":"100."},{"id":4,"category":"C","amount":"1000."},{"id":2,"category":"A","amount":"11."}]

Another example that uses JSON_AGG with GROUP BY:

SELECT category, JSON_AGG(id, amount) as Transactions 
FROM TestDb.test_table
group by category;

Output:

	category	Transactions
1	B	[{"id":5,"amount":"101."},{"id":6,"amount":"102."},{"id":4,"amount":"100."}]
2	C	[{"id":5,"amount":"1001."},{"id":6,"amount":"1002."},{"id":4,"amount":"1000."}]
3	A	[{"id":3,"amount":"12."},{"id":1,"amount":"10."},{"id":2,"amount":"11."}]

Use JSON_COMPOSE function

JSON_COMPOSE function can be used to create more complex JSON document when using in conjunction with JSON_AGG.

Example:

SELECT JSON_Compose(C.category, C.Transactions)
FROM
(
SELECT category, JSON_AGG(id, amount) as Transactions 
FROM TestDb.test_table
group by category
) AS C;

Output:

	JSON_COMPOSE(category AS category,Transactions AS Transactions)
1	{"category":"B","Transactions":[{"id":5,"amount":"101."},{"id":6,"amount":"102."},{"id":4,"amount":"100."}]}
2	{"category":"C","Transactions":[{"id":5,"amount":"1001."},{"id":6,"amount":"1002."},{"id":4,"amount":"1000."}]}
3	{"category":"A","Transactions":[{"id":3,"amount":"12."},{"id":1,"amount":"10."},{"id":2,"amount":"11."}]}
Have fun with Teradata JSON. 
info Last modified by Administrator at 22 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer hive local_offer SQL local_offer hive-sql-ddl

visibility 13
thumb_up 0
access_time 27 days ago

This page shows how to create partitioned Hive tables via Hive SQL (HQL). Create partition table Example: CREATE TABLE IF NOT EXISTS hql.transactions(txn_id BIGINT, cust_id INT, amount DECIMAL(20,2),txn_type STRING, created_date DATE) COMMENT 'A table to store transactions'...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 753
thumb_up 0
access_time 6 months ago

OREPLACE functions in Teradata can be used to replace or remove characters from a string. OREPACE is Teradata's extension to ASNI SQL. The usual REPLACE function is not available. ANSI SQL REPLACE function REPLACE function is commonly implemented in many other SQL databases such as ...

local_offer teradata local_offer SQL

visibility 9
thumb_up 0
access_time 2 days ago

This article demonstrates how to create volatile table in a Teradata procedure, perform DML actions (INSERT, DELETE, UPDATE) against it and then return the result set dynamically from the temporary table in the procedure.

About column

Teradata

Tutorials and information about Teradata.

rss_feed Subscribe RSS