JSON Data Type | Dot Notation in a WHERE Clause Examples | Teradata Vantage - Examples: Using Dot Notation in the WHERE Clause - Advanced SQL Engine - Teradata Database

Retrieve the Name of a Customer Who Has Ordered a Disk

SELECT jsonCol01.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol01.items..name);

> CustomerName

SELECT jsonCol02.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol02.items..name);

> CustomerName

SELECT jsonCol03.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol03.items..name);

> CustomerName

SELECT jsonCol04.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ANY(jsonCol04.items..name);

> CustomerName

SELECT jsonCol01.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 'disk' = ALL(jsonCol01.items..name);

*** 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);

> CustomerName 

SELECT jsonCol02.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 40 < ANY(jsonCol02.items..amt);

> CustomerName 

SELECT jsonCol03.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 40 < ANY(jsonCol03.items..amt);

> CustomerName 

SELECT jsonCol04.customer 
FROM jsonEnhancedDotNotationTbl
WHERE 40 < ANY(jsonCol04.items..amt);

> CustomerName