16.20 - JSON_KEYS - Teradata Vantage NewSQL Engine

Purpose

The JSON_KEYS table operator parses a JSON instance (represented as CHAR, VARCHAR, CLOB, or JSON) and returns a list of key names.

Syntax

TD_SYSFNLIB

The name of the database where the function is located.

json_expr

An expression that evaluates to correct JSON syntax. The expression can be a CHAR, VARCHAR, CLOB, or JSON representation of a JSON data type in LATIN or UNICODE.

USING name (value)

USING DEPTH (value) or USING QUOTES (value)

USING DEPTH (value)

An optional clause that restricts the depth of the search. value is a positive integer that specifies the maximum depth for the search.

The default value is the maximum depth of the JSON instance.

For example, setting the depth to 1 gets the top level keys in the JSON instance.

USING QUOTES (value)

An optional clause that specifies whether or not the key names in the result are enclosed in double quotation marks as follows:

• If value is 'Y' or 'y', the key names in the result are enclosed in double quotation marks. This is the default if the USING QUOTES clause is not specified.
• If value is 'N' or 'n', the key names in the result are not enclosed in double quotation marks.

AS

An optional keyword introducing correlation_name.

correlation_name

An alias for the input column specified by the ON clause.

ANSI SQL refers to aliases as correlation names. They are also referred to as range variables.

Return Values

JSON_KEYS performs a search on the JSON instance to the specified depth and returns a VARCHAR column of key names.

If a JSON array is present in the document, one result per index of each array is generated.

All results are given according to their "path" in the JSON document. Therefore, the output corresponding to a nested key will contain all of the parent keys, in addition to itself.

If you specify the USING QUOTES ('Y') clause, the key names in the result set are enclosed in double quotation marks. This is the default behavior and allows you to copy and paste a path into an SQL statement, using the path as an entity reference on a JSON document, without any potential for improper use of SQL keywords.

If you specify the USING QUOTES ('N') clause, the key names in the result set are not enclosed in double quotation marks. This allows you to use the output as input to one of the JSON extraction methods, such as JSONExtractValue.

JSON_KEYS returns an empty string when the input to the table operator is an empty JSON object.

Usage Notes

JSON_KEYS can be used on JSON data that is stored in text format as well as in one of the binary formats.