JsonExtract

Description

Extracts the value referenced by JSONPath-like expression json_path from a given json string (a string containing JSON) and returns the result as a JSON string.

Syntax

JsonExtract(string_expression, json_path)

Arguments

   string_expression - JSON string

  json_path - JSONPath expression string

Examples

JsonExtract('{ "store": { "book": "book1" } }', '$.store.book') returns the JSON string '"book1"'.

Return value datatype

string

Notes

Special characters in JSON keys require use of the associative array notation. Note that string escaping is required as well. For example, if your JSONPath contains a $ sign, both the dollar sign and the single quotes that qualify the key should be escaped:

JsonExtract(field1, '$.store.[\'\$book\']') 

Using JSONPath to filter a JSON string is another example in which single quotes require escaping. For example:

JsonExtract(field1, '$.store.books[?(@.name==\'Introduction to data integration\')]') 

If the string_expression is not a valid JSON or is the string value 'null' exceptions will be raised.

For more information, read How do I process JSON data.

Impact of null value

If input is null or there are no matches for the JSONPath expression, returns null.