Mastering Advanced SQL Techniques with MySQL/

...

/

Searching JSON Values

Searching JSON Values

Learn more about searching values in JSON arrays and objects.

We'll cover the following...

As a relational database management system, MySQL provides easy access to large amounts of data. Using SQL, data can be queried efficiently, enabling successful data analysis. For tables with columns of primitive data types, the SQL syntax we have learned about so far is sufficient. However, with the introduction of the JSON data type, the existing syntax does not allow us to search through JSON documents. Therefore, JSON documents are currently inaccessible to our knowledge. Let’s explore the operators and functions that MySQL introduces to make JSON documents searchable with our existing query workflow.

Retrieval

Retrieval of JSON values in MySQL involves using the -> and ->> operators to extract values from a JSON object at a specific JSON path. The -> operator returns the extracted value as data type JSON, while the ->> operator returns an unquoted version of the extracted value (i.e., a string that contains no double quotes or escapable characters). The closely related JSON_EXTRACT() function is an alternative to the -> operator and can extract multiple values simultaneously. Similarly, the JSON_VALUE() function retrieves scalar values from a JSON object.

The -> and ->> operators

Given a JSON object, the -> and ->> operators are essential in extracting specific values. Referring to our previously used Product table, we can easily access attributes of a product using the operators:

Press + to interact
-- Retrieve each product's ID
SELECT source -> '$.id' AS ID
FROM Product;
-- Insert empty lines in the output for better readability
SELECT "";
-- Retrieve each product's ID
SELECT JSON_EXTRACT(source, '$.id') AS ID
FROM Product;
-- Insert empty lines in the output for better readability
SELECT "";
-- Retrieve each product's last image
SELECT source -> '$.images[last]' AS `Last Image`
FROM Product;
-- Insert empty lines in the output for better readability
SELECT "";
-- Retrieve each product's last image
SELECT source ->> '$.images[last]' AS `Last Image`
FROM Product;
-- Insert empty lines in the output for better readability
SELECT "";
-- Retrieve each product's last image
SELECT JSON_UNQUOTE(JSON_EXTRACT(source, '$.images[last]')) AS `Last Image`
FROM Product;
-- Insert empty lines in the output for better readability
SELECT "";
-- Retrieve each product's ID together with its images
SELECT JSON_EXTRACT(source, '$.id', '$.images')
FROM Product;
...