The JSONExtract, JSONExtractValue, and JSONExtractLargeValue methods extract values from a JSON instance.
- JSONExtract
- Extracts data from a JSON instance. The desired data is specified in a JSONPath expression. The result is a JSON array composed of the values found, or NULL if there are no matches.
- JSONExtractValue
- Allows you to retrieve the text representation of the value of an entity in a JSON instance, specified using JSONPath syntax.
- JSONExtractLargeValue
- JSONExtractLargeValue is the same as JSONExtractValue, except for the return type and size.
The following table shows the main differences between the 3 methods.
| Method | Return Value | Return Type | Can Extract Multiple Values |
|---|---|---|---|
| JSONExtract | An array of values or a JSON null | JSON array | Yes |
| JSONExtractValue | A scalar value or a JSON null | VARCHAR | No |
| JSONExtractLargeValue | A scalar value or a JSON null | CLOB | No |
Usage considerations:
- By default, JSONExtractValue returns a VARCHAR(4096) of the desired character set. If the result cannot be contained in the return length of this method, use the JSONExtractLargeValue method instead.
- JSONExtractLargeValue functions identically to JSONExtractValue, but returns a CLOB of 16776192 characters for CHARACTER SET LATIN or 8388096 characters for CHARACTER SET UNICODE, depending on the character set of the JSON type that invoked it.
- The JSONExtractValue and JSONExtractLargeValue methods only extract a single scalar value or a JSON null. If more than one value matches the JSONPath query expression, a warning and an error message string indicating that multiple results were found are returned. Use JSONExtract if you want to extract multiple values from the JSON instance.
The reason to choose one method over the other depends on how you want to use the result. For example, if you want to extract a single scalar value from a JSON instance and treat the value as a predefined type castable from VARCHAR, use JSONExtractValue. If you want to extract a nested JSON object/array and do further processing on the data, treating the extracted item as a JSON object, use JSONExtract.