JSON Data Type | Extract JSON Data Using JSON Methods | Teradata Vantage - Extracting JSON Data Using SELECT and JSON Methods - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
uwa1591040057999.ditamap
dita:ditavalPath
uwa1591040057999.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢

JSON data can be extracted from JSON documents stored in the database using the SELECT statement or by using JSON methods: JSONExtract, JSONExtractValue, JSONExtractLargeValue, JSON_TABLE, JSON_SHRED_BATCH, and JSON_SHRED_BATCH_U.

To adhere to the proposed ANSI SQL/JSON standard, the extraction methods return one value that matches the desired path. The first result found is returned, except if one of the following elements is present in the JSONPath query string.

  • Recursive descent operation
  • Wildcard
  • Name or index list
  • Index slice
  • Filter
The JSONExtractValue and JSONExtractLargeValue methods only extract a single scalar value or a JSON null. If more than one value matches the JSONPath query expression, a warning and an error message string indicating that multiple results were found are returned.

The search is optimized in that it does not always need to search the entire document. However, this means that the following scenario is possible.

  1. Disable JSON validation
  2. Insert malformed JSON data. For example, something similar to this: {"name":"Cameron" 123456}
  3. Enable JSON validation
  4. Attempt to extract the value of the key "name"

In this case, the correct result of "Cameron" is returned, because the result was found before passing through the entire document and reaching the malformed data. If any malformed data is encountered during the search, before the result is reached, a syntax error is reported.