JSON Data Type | SELECT with Dot Notation Examples | Teradata Vantage - Examples: Using Dot Notation in the SELECT List - Advanced SQL Engine - Teradata Database

JSON Data Type
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
uwa1591040057999.ditamap
dita:ditavalPath
uwa1591040057999.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantage™

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.