JSONExtract Function

Description Pick out one or more portions of a JSON input string as a single value, an array of values, a JSON object, or list of items, via a standard JSONPath expression.
Remark Takes two arguments:
  • JSONtoParse: The input JSON to parse and return the value(s) from
  • JSONPath: A JSONPath expression that describes what value(s) to return from JSONtoParse

By default, JSONExtract is done in-memory unless the system administrator explicitly sets a hidden system configuration flag to translate JSONExtract to SQL for processing in the Data Source. JSONPath is a standardized format for accessing elements of a JSON structure.
Examples 
JSONtoParse = { 
	"products": [ 
		{ "name": "Côte de Blaye", "price": 263.50 }, 
		{ "name": "Ipoh Coffee", "price": 46.0 }, 
		{ "name": "Vegie-spread", "price" : 43.9 }, 
		{ "name": "Northwoods Cranberry Sauce", "price": 40 } 
	]
}

JSONExtract(JSONtoParse, "$..price") returns [263.5,46.0,43.9,40]
JSONExtract(JSONtoParse, "$..products[3].name")returns [“Northwoods Cranberry Sauce”]

JSONtoParse = {"color": "green", "code": "123"}

JSONExtract(JSONtoParse, "$.color") returns green
JSONExtract(JSONtoParse, "$.code") returns 123

JSONExtract in the Data Source

By default, the application will extract JSON in-memory. The system administrator can enable JSON extraction in the database, for those Data Sources that support it, by changing a system configuration setting. Each Data Source implements this function differently, so care should be taken when enabling this feature.

Table A below lists the supported JSONPath expressions for in-memory processing and processing in the supported Data Source types.

Table A — Supported JSONPath Expressions in Data Sources
JSONPath Feature In-Memory MySQL Oracle SQL Server 2016+
$ Yes Yes No No
$.prop Yes Yes Yes Yes
$.prop[0] Yes Yes Yes Yes
$[1,2] Yes No No No
$..recursive Yes No No No
$.prop.* Yes Yes No No
$[*] Yes Yes No No
$[3:5] Yes No No No
$[6:] Yes No No No
$[:3] Yes No No No
$[-2:] Yes No No No
$[?(@.prop == val)] No No No No
$[?(@.prop != val)] No No No No
$[?(@.prop > val)] No No No No
$[?(@.prop < val)] No No No No
$[?(@.prop >= val)] No No No No
$[?(@.prop <= val)] No No No No
$[?(@.prop =~ val)] No No No No
$[?([email protected] =~ val)] No No No No
$[?(@.prop == val && @.prop == val)] No No No No
$[?(@.prop == val || @.prop == val)] No No No No
$[?(@.prop in ['1', '2', '3'])] No No No No
$[?(@.prop nin ['1', '2', '3'])] No No No No

Notes and Exceptions

  • IBM DB2 — testing has shown this data source does not support JSONExtract although its documentation says that it does
  • PostgreSQL — no support for JSONPath syntax, so JSONExtract can only be done in-memory
  • Oracle — if the JSONPath expression selects a JSON object from within the main JSON object, the return value will be null