Examples: Using Dot Notation in the WHERE Clause

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.

Retrieve the Name of a Customer Who Has Ordered a Disk

SELECT jsonCol01.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol01.items..name);
Result:
> CustomerName
SELECT jsonCol02.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol02.items..name);
Result:
> CustomerName
SELECT jsonCol03.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol03.items..name);
Result:
> CustomerName
SELECT jsonCol04.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol04.items..name);
Result:
> CustomerName
SELECT jsonCol01.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ALL(jsonCol01.items..name);
Result:
*** No rows found

Retrieve the Name of a Customer Who Ordered More Than 40 Pieces of an Item

SELECT jsonCol01.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 40 < ANY(jsonCol01.items..amt);
Result:
> CustomerName 
SELECT jsonCol02.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 40 < ANY(jsonCol02.items..amt);
Result:
> CustomerName 
SELECT jsonCol03.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 40 < ANY(jsonCol03.items..amt);
Result:
> CustomerName 
SELECT jsonCol04.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 40 < ANY(jsonCol04.items..amt);
Result:
> CustomerName