Retrieving JSON Data Using SELECT

Teradata Vantageā„¢ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

You can use the SELECT statement to retrieve data from JSON columns.

In field mode, the SELECT result is always a string, even if it is filtered using one of the JSON methods.

If a string is not the desired format, you must cast the result set to the proper data type it represents.

In non-field mode, the JSON type is returned to the client.

Retrieval of data from a JSON column stored as BSON or UBJSON will always result in a conversion of the data to its text format.

You cannot include a JSON column in the ORDER BY, HAVING or GROUP BY clauses of a SELECT statement.

In the SELECT or WHERE clause, you can add a JSON entity reference to the end of a column reference or any expression which evaluates to a JSON type.

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" : 8584852612}'));
INSERT INTO my_table (2, NEW JSON('{"name" : "Justin", "phoneNumber" : 8584852611}'));

SELECT edata FROM my_table;

Result:

'{"name" : "Justin", "phoneNumber" : 8584852611}'
'{"name" : "Cameron", "phoneNumber" : 8584852612}'

Example: Retrieve 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" : 8584852612}'