Examples: Ambiguous JSON Dot Notation - Advanced SQL Engine - 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 JSON dot notation.

SELECT id, 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 dot notation 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 incorrect JSON dot notation 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 dot notation 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 a dot notation 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 JSON dot notation.

SELECT jsonTable2.id, jsonTable2.jsonCol.name
FROM test.jsonTable3;

Result:

id     jsonCol.name
------------------------------
1      Cameron