JSON_PATH
The JSONPATH function provides XPath-like syntax for JSON structures. It provides a way to extract parts of a given document.
Syntax
JSONPATH (json_object, search_query [, arguments] )
Remarks
• The search returns a JSON array as a string, or FALSE if the search fails.
• The search query starts with a dollar sign to represent the root object.
Example
PROCEDURE JSONPathFunctionExample(OUT resultJson VARCHAR)
BEGIN
DECLARE sourceJson VARCHAR(4096);
DECLARE jsonPathExpression VARCHAR(4096);
-- Create a JSON value to use in the JSONPATH function.
SET sourceJson = '{"LookupProductResponse":{"LookupProductResult":{"row":[{"ProductName":"Maxtific 40GB ATA133 7200","ProductID":"1","ProductDescription":"Maxtific Storage 40 GB"}]}}}';
-- Create a JSONPATH expression to evaluate.
SET jsonPathExpression = '$.LookupProductResponse.LookupProductResult.row[0].ProductName';
-- Evaluate the XPATH expression against the source XML value.
SET resultJson = JSONPATH (sourceJson, jsonPathExpression);
END
The result is Maxtific 40GB ATA133 7200.