JSON Data Type

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

Product

Advanced SQL Engine

Teradata Database

Release Number

17.05

17.00

Published

September 2020

Language

English (United States)

Last Update

2021-01-23

dita:mapPath

gzn1554761068186.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1150

lifecycle

Product Category

Teradata Vantage™

You can compare a list of results retrieved by a dot notation query to a single operand in a WHERE clause. Only one dot notation query is permitted per condition in the WHERE clause. Multiple dot notation queries in one condition must each evaluate to one single result. Otherwise, the comparison will not be valid and an error accompanied by an appropriate error string may occur at runtime if some implicit conversion fails.

The default return value is unchanged from other dot notation queries in other SQL clauses. Conversion from character data to whatever data type is needed to perform the comparison is handled implicitly, and in the case of a list of results, it occurs for each item in the list individually.

Dot notation can be used as part of a larger expression in a condition of the WHERE clause. When a list of results is returned, each individual item is passed on to be evaluated by the rest of the expression before evaluating the condition.

To achieve this processing of each item in a list in the WHERE clause, the ANY/SOME/ALL SQL operators are overloaded as follows:

When ANY/SOME bound a dot notation query, all results are compared against the other operand, and if ANY comparison results in true, the condition evaluates to true. For example:
```
SELECT 1 WHERE 1 >= ANY(NEW JSON('{"a":1, "a":2}')..a);
```
The result is 1.
When ALL bounds a dot notation query, all results are compared against the other operand, and if ALL comparison results in true, the condition evaluates to true. For example:
```
SELECT 1 WHERE 2 >= ALL(NEW JSON('{"a":1, "a":2}')..a);
```
The result is 1.
When there is neither ANY nor SOME nor ALL bounding a dot notation query, the list of results is compared against the other operand. For example:
```
SELECT 1 WHERE '[1,2]' = NEW JSON('{"a":1, "a":2}')..a;
```
The result is 1.
When the method style syntax is used, the list of results is compared against the other operand. For example:
```
SELECT 1 WHERE '[1,2]' = NEW JSON('{"a":1, "a":2}').JSONExtractValue('$..a','list');
```
The result is 1.

These rules apply to all dot notation queries regardless of data type. If this atomized evaluation is not desired, comparison of an entire list may be performed by omitting the ANY/SOME/ALL clause from the condition, or using the method style syntax.

Column references may only be scoped to tables in the current database or any tables in the query. You will get an error if you try to qualify a column to the database level. For example, the following query fails:

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

Result:

*** Failure 3807 Object 'databasename' does not exist.