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