Examples: Ambiguous JSON Entity Reference - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantageā„¢ JSON Data Type

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

Example

In the following query, T.jsonCol and T.id are interpreted as column references.
SELECT T.id, T.jsonCol
FROM test.jsonTable T
WHERE id < 3
ORDER BY 1;
Result:
id     jsonCol
--------------
1      {"name" : "Cameron", "numbers" : [1,2,3,[1,2]]}
2      {"name" : "Cameron", "name" : "Lewis"}

Example

In the following query, jsonCol.name is interpreted as a JSON entity reference.
SELECT jsonCol.name
FROM test.jsonTable
WHERE id=1;
Result:
id     jsonCol.name
-------------------
1      Cameron

Example

The following query returns an error because there is more than one source table with a JSON column named jsonCol.
SELECT jsonCol.name
FROM test.jsonTable, test.jsonTable2;
Result:
*** Failure 3809 Column 'jsonCol' is ambiguous.

Example

The following query shows a JSON entity reference specified as a fully qualified column reference.
SELECT id, test.jsonTable.jsonCol.name
FROM test.jsonTable
WHERE id=1;
Result:
id     jsonTable.jsonCol.name
----------------------------------
1      Cameron

Example

The following shows an incorrect JSON entity reference specified as a fully qualified column reference.
SELECT test.jsonTable.id.name
FROM test.jsonTable
WHERE id=1;
The query returns an error.
*** Failure 3706 Syntax error: Invalid use of JSON entity reference syntax on non-JSON type.

Example

In the following query, jsonTable.jsonCol.name is a JSON entity reference that looks like a database.table.column reference.
SELECT id, jsonTable.jsonCol.name
FROM test.jsonTable
WHERE id=1;
Result:
id     jsonTable.jsonCol.name
-----------------------------
1      Cameron

Example

Incorrect JSON entity reference

SELECT jsonTable.id.name
FROM test.jsonTable
WHERE id=1;
Result:
*** Failure 3802 Database 'jsonTable' does not exist.

Example

In the following query, jsonCol.name."first" is interpreted as an entity reference on the jsonCol column of the source table, test.jsonTable.
SELECT T.id, jsonCol.name."first"
FROM test.jsonTable T, test.jsonTable3 T3
ORDER BY T.id;
Result:
id     jsonCol.name.first
-------------------------
1      ?
2      ?
3      Cameron

Example

In the following query, the reference to jsonCol is ambiguous because both source tables have a JSON column named jsonCol.
SELECT T.id, jsonCol.name."first"
FROM test.jsonTable T, test.jsonTable2 T2
ORDER BY T.id;
The query returns an error.
*** Failure 3809 Column 'jsonCol' is ambiguous.

Example

In this example, jsonTable2 is in the current database and it has a JSON column called jsonCol, so jsonTable2.jsonCol.name is interpreted as a JSON entity reference.
SELECT jsonTable2.id, jsonTable2.jsonCol.name
FROM test.jsonTable3;
Result:
id     jsonCol.name
------------------------------
1      Cameron