Extract JSON Data via SQL Functions in Teradata

access_time 3 months ago visibility120 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 3 months 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 teradata local_offer teradata-utilities

visibility 108
thumb_up 0
access_time 3 months ago

BTEQ is a Teradata utility tool that can be used to run Teradata SQL statements incl. DDL, DML, etc. It can also be used to import data from text file into Teradata databases. It also works with XML and JSON files too. Like TPT and FASTLOAD, it can run in both batch and interactive modes. This ...

local_offer teradata local_offer teradata-sql-query

visibility 5110
thumb_up 0
access_time 3 years ago

This page summarize some of the commonly used views in Teradata. In all the views in the following sections, X views are also available though they only return rows that contain information on objects that the requesting database user owns, created, granted privilige on, granted access through the ...

local_offer teradata local_offer SQL

visibility 200
thumb_up 0
access_time 3 months ago

Function CURRENT_TIMESTAMP can be used to retrieve the current timestamp: SELECT CURRENT_TIMESTAMP; Sample output: 20/09/2020 20:55:35.390000-04:00 Function CAST can be used to convert TimeStamp to DATE. SELECT CAST(CURRENT_TIMESTAMP AS DATE) Sample output: 20/09/2020 SELECT ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS