Translation reference to convert the Teradata functions JSONExtractValue, JSONExtractLargeValue and JSONExtract to Snowflake Scripting.


As per Teradata’s documentation, these functions use the JSONPath Query Syntax to request information about a portion of a JSON instance. The entity desired can be any portion of a JSON instance, such as a name/value pair, an object, an array, an array element, or a value.

For more information regarding Teradata JSONExtractValue, JSONExtractLargeValue and JSONExtract, check here.




The JSON_EXTRACT_UDF is a Snowflake implementation of the JSONPath specification that uses a modified version of the original JavaScript implementation developed by Stefan Goessner.

Sample Source Pattern


IN -> Teradata_01.sql
    Store.JSONExtract('$..author') as AllAuthors,
    Store.JSONExtractValue('$..book[2].title') as ThirdBookTitle,
    Store.JSONExtractLargeValue('$..book[2].price') as ThirdBookPrice
FROM BookStores;

Snowflake Scripting

OUT -> Teradata_01.sql
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) as AllAuthors,
    JSON_EXTRACT_UDF(Store, '$..book[2].title', TRUE) as ThirdBookTitle,
    JSON_EXTRACT_UDF(Store, '$..book[2].price', TRUE) as ThirdBookPrice

Some parts in the output code are omitted for clarity reasons.

Known Issues

1. Elements inside JSONs may not retain their original order.

Elements inside a JSON are ordered by their keys when inserted in a table. Thus, the query results might differ. However, this does not affect the order of arrays inside the JSON.

For example, if the original JSON is:

   "cities": ["Los Angeles", "Lima", "Buenos Aires"]

Using the Snowflake PARSE_JSON() that interprets an input string as a JSON document, producing a VARIANT value. The inserted JSON will be:

   "age": 31,
   "cities": ["Los Angeles", "Lima", "Buenos Aires"],
   "firstName": "Peter",
   "lastName": "Andre" 

Note how "age" is now the first element. However, the array of "cities" maintains its original order.

No related EWIs.

Last updated