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.
- If standard resolution is successful, the reference is interpreted as a standard table.column reference.
- 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.
- 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.
- 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.
- Otherwise if standard resolution is not successful, the standard disambiguation logic is used as follows:
- 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.
- 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.
- 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.
- Otherwise, processing continues with standard error handling.