JSON_EXTRACT

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

Description

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.

JSON_expr.JSONExtractValue(JSONPath_expr)

JSON_expr.JSONExtractLargeValue(JSONPath_expr)

JSON_expr.JSONExtract(JSONPath_expr)

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

Teradata

Query

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

Snowflake Scripting

Query

SELECT
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) as AllAuthors,
    JSON_EXTRACT_UDF(Store, '$..book[2].title', TRUE) as ThirdBookTitle,
    JSON_EXTRACT_UDF(Store, '$..book[2].title', TRUE) as ThirdBookPrice
FROM BookStores;

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:

{ 
   "firstName":"Peter",
   "lastName":"Andre",
   "age":31,
   "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.

Related EWIs

1. MSCEWI1020: Custom UDF inserted.