How to Retrieve a Specific Object from a JSON Field in a MySQL Table by Key Value?

Finding Objects in MySQL JSON by Key Value

Scenario:

You're working with a JSON field in a MySQL table and want to retrieve a specific object from within it while searching for a particular key value.

JSON Document Structure:

Consider the following schema:

<code class="sql">CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;</code>

With sample data:

<code class="sql">INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, {"text": "Synthetic  (Old Type - Hard)", "value": "3"}, {"text": "Gravel", "value": "5"}, {"text": "Clay", "value": "6"}, {"text": "Sand", "value": "7"}, {"text": "Grass/Synthetic Mix", "value": "8"}]'
);</code>

Challenge:

You need to select the "Grass" text value from the options field based on a search for the value "1".

MySQL 8.0 Solution:

MySQL 8.0 introduced the JSON_TABLE() function, providing a more efficient solution:

<code class="sql">select field_options.* from fields cross join 
json_table(fields.options, 
'$[*]' columns(
  text text path '$.text',
  value text path '$.value'
 )
) as field_options 
where field_options.value = 1;</code>

This query uses the cross join and JSON_TABLE() to retrieve the text and value columns for each object in the options field.

Concerns with JSON_TABLE:

However, using JSON_TABLE() can be complex and must be repeated every time a query like this is needed.

Recommendation:

Instead of using JSON, it may be simpler to store data in a table with dedicated columns for the text and value pairs. This would allow for straightforward searches using regular SQL syntax:

<code class="sql">SELECT * FROM field_options WHERE value = '1';</code>

The above is the detailed content of How to Retrieve a Specific Object from a JSON Field in a MySQL Table by Key Value?. For more information, please follow other related articles on the PHP Chinese website!