You can use the SELECT statement with the JSON dot notation syntax or the JSONExtractValue (or JSONExtractLargeValue) method to extract a single scalar value from a JSON instance. However, if you try to use JSON dot notation or the JSONExtractValue (or JSONExtractLargeValue) method to extract more than one value, the database returns a warning and an error message string by default.
The following query returns a warning and error message string because it uses JSON dot notation to extract more than one object member. If you want a list of values returned instead, you must specify this behavior using the SET SESSION DOT NOTATION ON ERROR statement or the DotNotationOnErrorCondition DBS Control field. Note that using the following syntax elements in JSON dot notation returns multiple results:
- Recursive descent operator
- Wildcard
- Name or index list
- Slice
SELECT NEW JSON('{"name":"Al","name":"Betty"}').name;
Result:
*** Query completed. One row found. One column returned. *** Warning: 7548 More than one result per JSON instance found. *** Total elapsed time was 1 second. NEW JSON('{"name":"Al","name":"Betty"}', LATIN).name ----------------------------------------------------------------- *** ERROR MULTI RESULT ***
The same query using JSONExtractValue instead of dot notation also returns a warning and an error message string.
SELECT NEW JSON('{"name":"Al","name":"Betty"}').JSONExtractValue('$.name');
Result:
*** Query completed. One row found. One column returned. *** Warning: 7548 More than one result per JSON instance found. *** Total elapsed time was 1 second. NEW JSON('{"name":"Al","name":"Betty"}', LATIN).JSONEXTRACTVALUE('$.name') --------------------------------------------------------------------------- *** ERROR MULTI RESULT ***
You can use the JSONExtract method to extract multiple values. The same query using the JSONExtract method succeeds and returns the multiple values in a JSON array.
SELECT NEW JSON('{"name":"Al","name":"Betty"}').JSONExtract('$.name');
Result:
*** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. NEW JSON('{"name":"Al","name":"Betty"}', LATIN).JSONEXTRACT('$.name') --------------------------------------------------------------------------- ["Al","Betty"]