Extract JSON Data via SQL Functions in Teradata

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. 

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 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."}]}