JSON_GET_POS_ARR_INDEX

The JSON_GET_POS_ARR_INDEX function searches array values without having to extract the array values with the JSON_TABLE function.

Read syntax diagramSkip visual syntax diagram JSON_GET_POS_ARR_INDEX ( json-value , search-string )

The schema is SYSTOOLS.

json-value
An expression that returns a BLOB value. The json-value must contain a BSON representation of a JSON document.
search-string
The format of search-string is {field:value} and it needs to be in BSON format. You need to add the JSON2BSON function around the search-string specification:
JSON2BSON( '{"field":"value"}' )

The result of the function is an integer.

This function only tests for equivalence and the data type must match what is already in the field. The return value is the position within the array that the value was found, where the first element starts at zero. If the value is not present, then the function returns -1.

Example

The following SQL retrieves all employees who have the extension 1422:
SELECT JSON_VAL(EMP_DATA, 'lastname', 's:10') AS LASTNAME
   FROM JSON_EMP
WHERE JSON_GET_POS_ARR_INDEX(EMP_DATA, JSON2BSON('{"phoneno":1422}')) >= 0;