You can use the SELECT statement to retrieve data from JSON columns.
In field mode, the SELECT result is always a string, even if it is filtered using one of the JSON methods.
If a string is not the desired format, you must cast the result set to the proper data type it represents.
In non-field mode, the JSON type is returned to the client.
Retrieval of data from a JSON column stored as BSON or UBJSON will always result in a conversion of the data to its text format.
In the SELECT or WHERE clause, you can add a JSON entity reference to the end of a column reference or any expression which evaluates to a JSON type.
Setting Up the SELECT Statement Example
Create and populate table(s) to use in subsequent example(s).
CREATE TABLE my_table (eno INTEGER, edata JSON(100));
INSERT INTO my_table (1, NEW JSON('{"name" : "Cameron", "phoneNumber" : 8584852612}'));
INSERT INTO my_table (2, NEW JSON('{"name" : "Justin", "phoneNumber" : 8584852611}'));
SELECT edata FROM my_table;
Result:
'{"name" : "Justin", "phoneNumber" : 8584852611}'
'{"name" : "Cameron", "phoneNumber" : 8584852612}'
Example: Retrieve JSON Data Using the SELECT Statement
The example uses the JSONExtractValue method to extract data where the name is Cameron.
SELECT eno, edata
FROM my_table
WHERE edata.JSONExtractValue('$.name') = 'Cameron'
ORDER BY 1;
Result:
eno edata
--------
1 '{"name" : "Cameron", "phoneNumber" : 8584852612}'