15.10 - Retrieving JSON Data Using SELECT - Teradata Database

Teradata Database Teradata JSON

Teradata Database
December 2015
Programming Reference

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;


'{"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' 


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