Retrieving JSON Data Using SELECT Example | Teradata Vantage - Retrieving JSON Data Using SELECT Example - Advanced SQL Engine - Teradata Database

Setting Up the SELECT Statement Example

Create and populate table(s) to use in subsequent example(s).

CREATE TABLE my_table (eno INTEGER, edata JSON(100)); 

INSERT INTO my_table (1, NEW JSON('{"name" : "Cameron", "phoneNumber" : 9595552612}'));
INSERT INTO my_table (2, NEW JSON('{"name" : "Justin", "phoneNumber" : 9595552611}'));

SELECT edata FROM my_table;

Result:

'{"name" : "Justin", "phoneNumber" : 9595552611}'
'{"name" : "Cameron", "phoneNumber" : 9595552612}'

Retrieving JSON Data Using the SELECT Statement

The example uses the JSONExtractValue method to extract data where the name is Cameron.

SELECT eno, edata 
FROM my_table 
WHERE edata.JSONExtractValue('$.name') = 'Cameron' 
ORDER BY 1;

Result:

eno edata
--------
1   '{"name" : "Cameron", "phoneNumber" : 9595552612}'