GET_JSON_OBJECT

GET_JSON_OBJECT 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;