Searching JSON Values

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: