In Javascript, the native context for JSON, you can traverse JSON data using dot notation and array indexing, depending on the structure of the JSON instance. This is called a JSON entity reference, and you can use the following syntax to retrieve entities of a JSON instance. The JSON entity reference syntax can also be used on columns that use one of the optional binary storage formats.
Syntax
Syntax Elements
- JSON_expr
- An expression that evaluates to a JSON data type.
- Object Member
- A Teradata nonreserved word or a string literal enclosed in double quotation marks.
- integer
- A positive integer value that is within the range of the JSON instance being traversed.
Return Value
When the desired entity is found, the resulting data is a VARCHAR value.
If more than one entity is found which satisfies the specified syntax, a warning and an error message string indicating that multiple results were found are returned.
The default length of the return value is 4096 characters, but you can use the JSON_AttributeSize DBS Control field to change the default length. An error is returned if the result exceeds the specified length. The maximum length that can be set for the return value is 32000 characters.
For details about the JSON_AttributeSize DBS Control field, see Utilities, B035-1102.
If the entity is not found or if the JSON instance being referenced is null, a Teradata NULL is returned.
If the result is an empty string, an empty string is returned.
Restrictions
You cannot use a JSON entity reference in the target portion of a SET clause because you cannot update the entities of a JSON instance.
Ambiguity Between a JSON Entity Reference and References to Other Database Objects
The syntax for a JSON entity reference, a Teradata ARRAY type element reference, and a fully or partially qualified column reference is similar. This creates potential ambiguity in the following cases:
- Ambiguity between a Teradata ARRAY type element reference and a JSON type array reference
- Ambiguity between a fully or partially qualified column reference and a JSON type dot notation reference
When there is ambiguity, the default interpretation of the syntax is as follows:
- For case 1: The syntax is interpreted as a Teradata ARRAY type element reference.
- For case 2: The syntax is interpreted as a fully or partially qualified column reference.
Handling of these potential ambiguities is described in more detail in the following sections.
To ensure that a JSON entity reference is not ambiguous, you should fully qualify a JSON column being referenced. Alternatively, you can use the JSONExtract, JSONExtractValue, or JSONExtractLargeValue methods to retrieve entities of a JSON instance.