JsonExtractScalar

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 string. The value referenced by json_path must be a scalar (boolean, numeric or string).

Syntax

JsonExtractScalar(string_expression, json_path)

Arguments

   string_expression - JSON string

  json_path - JSONPath expression string

Examples

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

JsonExtractScalar('{ "store": { "books": ["book1","book2"] } }', '$.store.books') returns null.

Return value datatype

string

Notes

Special characters in JSON keys require use of the associative array notation with single quotes as string identifiers. 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:

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

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.

If the the value referenced by json_path is not a scalar (boolean, numeric or string) or others, null is returned.

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.

Feedback and Knowledge Base