15.10 - Examples: Ambiguous JSON Entity Reference - Teradata Database

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"}

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

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.

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

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.

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

SELECT jsonTable.id.name
FROM test.jsonTable
WHERE id=1;

Result:

*** Failure 3802 Database 'jsonTable' does not exist.

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

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.

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