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 stringExamples
JsonExtract('{ "store": { "book": "book1" } }', '$.store.book') returns the JSON string ‘“book1”’.
Return value datatype
stringNotes
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.