Using Dot Notation in a 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

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.