access_time 12 months ago languageEnglish
more_vert

Extract JSON Data via SQL Functions in Teradata

visibility 775 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 12 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

More from Kontext

visibility 44
thumb_up 0
access_time 7 months ago
visibility 1012
thumb_up 0
access_time 11 months ago
visibility 78
thumb_up 0
access_time 4 months ago