GET_JSON_OBJECT
GET_JSON_OBJECT is a push-only function that extracts a JSON object from a JSON string based on the JSON path, and returns a JSON string of the extracted JSON object.
Syntax
GET_JSON_OBJECT (STRING json_string, STRING json_path)
Remarks
• The json_path argument can contain only numbers, lowercase letters, and underscore ( _ ).
• Keys cannot start with numbers because of restrictions on Hive/Hadoop column names.
• This function does not support recursive descent using '..'
• This function does not support filter expression '[?(<expression>)]
• Return value is NULL if the input JSON string is invalid.
• Union operator and array slice operator is not supported by this function.
Examples
The following is a simple example that uses GET_JSON_OBJECT.
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 output of this example is ‘Maxtific 40GB ATA133 7200’.
You can also use GET_JSON_OBJECT to iterate through an array and count the elements.
SET i = 0;
SET jsonobject = GET_JSON_OBJECT(jsonstring,'$.array_element[‘||CAST(i AS VARCHAR)||‘]') ;
WHILE jsonobject NOT NULL DO
SET i = i + 1 ;
SET jsonobject = GET_JSON_OBJECT(jsonstring,'$.array_element[‘||CAST(i AS VARCHAR)||‘]') ;
END DO;