Column Reference vs. JSON Type Dot Notation Reference

Teradata Vantage™ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

When parsing a request that features syntax matching both a fully or partially qualified column reference and a JSON type dot notation reference, the following logic is employed.

Column Reference with a Specified Table

The following logic is employed to differentiate between a standard column reference in the format table.column and a reference to an entity in a JSON instance in the format name1.name2.

  1. If standard resolution is successful, the reference is interpreted as a standard table.column reference.
  2. Otherwise, if there is one source table with a JSON column named name1, the reference is interpreted as a reference to an entity called name2 on a JSON column called name1.

    If there is more than one source table with a JSON column named name1, an ambiguity error is returned.

  3. If there are no source tables with a JSON column named name1, an error is returned.

Column Reference with a Specified Table and Database

The following logic is employed to differentiate between a standard column reference in the format database.table.column and a reference to an entity in a JSON instance in the format name1.name2.name3...nameN.

  1. If standard resolution is successful and there are more than 3 names, the reference is interpreted as a reference to an entity called name4…nameN on a JSON column called name1.name2.name3. An error is returned if the column is not a JSON column.

    If there are 3 names, the reference is interpreted as a standard database.table.column reference.

  2. Otherwise if standard resolution is not successful, the standard disambiguation logic is used as follows:
    1. If a source table named name1 exists and it has a JSON column named name2, the reference is interpreted as a reference to an entity called name3…nameN on a JSON column called name1.name2. Otherwise, an error is returned indicating that column name2 was not found.
    2. If there is one source table with a JSON column named name1, the reference is interpreted as a reference to an entity called name2…nameN on a JSON column called name1 that is present in one source table.

      If there is more than one source table with a JSON column named name1, an ambiguity error is returned.

    3. If there is a table in the current database named name1 with a JSON column named name2, the reference is interpreted as a reference to an entity called name3…nameN on a JSON column called CurrentDatabase.name1.name2.
    4. Otherwise, processing continues with standard error handling.