16.10 - JSON Entity Reference (Dot Notation Syntax) - Teradata Database

Teradata Database JSON Data Type
Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
hcg1480972902258.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
evi1472243742653

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
Consists of a descent operator followed by a Name syntax element.
A descent operator is one of the following:
  • A child operator '.'
  • A recursive descent operator '..'
Name
A Teradata nonreserved word or a string literal enclosed in double quotation marks.
Array Element
Consists of an optional recursive descent operator '..' followed by an index value enclosed in brackets.
An index value is one of the following:
  • An unsigned INTEGER value
  • A wildcard operator '*'
Wildcard
Consists of a descent operator followed by the wildcard character '*'.
A descent operator is one of the following:
  • A child operator '.'
  • A recursive descent operator '..'
The wildcard operator can be used both in reference to named and indexed items.
Name or Index List
Consists of an optional recursive descent operator '..' followed by two or more comma-separated list values enclosed in brackets.
A list value is one of the following:
  • A Name syntax element
  • An unsigned INTEGER value
Examples:
  • Name list, such as [a,b,c]
  • Index list, such as [0,3,5]
Slice
Consists of an optional recursive descent operator '..' followed by 2 or 3 colon-separated INTEGER values enclosed in brackets.
For example, [0:5:3]

Return Value

A query will likely return multiple results if any of the following syntax elements are present in the dot notation string:
  • Recursive descent operator
  • Wildcard
  • Name or index list
  • Slice

A dot notation query which includes one or more of these syntax elements may return a list of results if necessary.

When using dot notation syntax to retrieve a JSON object member or an array element, the resulting data is a VARCHAR value. In the event that 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. If you want a list of values to be returned instead of an error string in these cases, do one of the following:
  • Set the DotNotationOnErrorCondition DBS Control field as appropriate.
    This DBS Control field modifies the behavior of all dot notation queries across the entire system, so caution must be used to properly set this flag to accommodate the needs of all users.
  • Use the SET SESSION DOT NOTATION...ON ERROR statement to specify the behavior on a session by session basis instead of at the system level.

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.

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.

For details about the DotNotationOnErrorCondition and JSON_AttributeSize DBS Control fields, see Utilities.

For details about the SET SESSION DOT NOTATION...ON ERROR statement, see SQL Data Definition Language - Syntax and Examples.

Usage Notes

Anything expressed in dot notation can be expressed by invoking the JSONExtractValue or JSONExtractLargeValue methods. Dot notation is provided as an optimization of Teradata SQL to provide easy access to elements of structured and semistructured data.

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.