Examples: Using Dot Notation in the SELECT List

Teradata Vantageā„¢ JSON Data Type

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

The examples in this section reference the table and data set up in Setting Up the Examples Using Dot Notation in SELECT and WHERE Clause.

The following SELECT statements show the use of the recursive descent operator (..):
SELECT jsonCol01..name FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol02..name FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol03..name FROM jsonEnhancedDotNotationTbl;
SELECT jsonCol04..name FROM jsonEnhancedDotNotationTbl;
The above queries all return the same result as follows:
> ["disk","RAM","monitor","keyboard","camera","button","mouse","pen"]

A list of results is returned instead of an error string because the dot notation query includes the recursive descent operator.

The following SELECT statements show the use of the wildcard operator (*):
SELECT jsonCol01.items[0].* FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol02.items[0].* FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol03.items[0].* FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol04.items[0].* FROM jsonEnhancedDotNotationTbl;
The above queries all return the same result as follows:
> [1,"disk",10]

A list of results is returned instead of an error string because the dot notation query includes the wildcard operator.

The following SELECT statements show the use of a named list operator:
SELECT jsonCol01[customer,orderID] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol02[customer,orderID] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol03[customer,orderID] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol04[customer,orderID] FROM jsonEnhancedDotNotationTbl;
The above queries all return the same result as follows:
> ["CustomerName",3]

A list of results is returned instead of an error string because the dot notation query includes a named list operator.

The following SELECT statements show the use of an index list operator:
SELECT jsonCol01.items[0,1] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol02.items[0,1] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol03.items[0,1] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol04.items[0,1] FROM jsonEnhancedDotNotationTbl;
The above queries all return the same result as follows:
> [{"ID":1,"name":"disk","amt":10},{"ID":2,"name":"RAM","amt":20}]

A list of results is returned instead of an error string because the dot notation query includes an index list operator.

The following SELECT statements show the use of a slice operator:
SELECT jsonCol01.items[0:4:2] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol02.items[0:4:2] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol03.items[0:4:2] FROM jsonEnhancedDotNotationTbl; 
SELECT jsonCol04.items[0:4:2] FROM jsonEnhancedDotNotationTbl;
The above queries all return the same result as follows:
> [{"ID":1,"name":"disk","amt":10},{"ID":3,"name":"monitor","amt":30}]

A list of results is returned instead of an error string because the dot notation query includes a slice operator.