How to Efficiently Retrieve JSON Objects in MySQL Based on Key Values?

Finding Objects based on Key Values in MySQL JSON

When working with JSON data in MySQL, locating an object based on a specific key value can be challenging. This is because traditional methods require knowledge of the array index or complex extraction techniques.

JSON_TABLE() to the Rescue

Fortunately, MySQL 8.0 introduced the JSON_TABLE() function, which simplifies this process. Consider the following schema:

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;

With data inserted as:

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"}]'
);

To search for the string "Grass" by providing the key "1", you can use the following query:

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;

This query will return the desired object:

+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+

Alternative: Traditional Table Structure

While JSON_TABLE() provides a solution, it can be cumbersome to use repeatedly. An alternative approach is to store data in a traditional table structure with separate columns for text and value, as follows:

CREATE TABLE field_options (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  text varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  value varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

With data inserted as:

INSERT INTO field_options (text, value) VALUES
('Grass', '1'),
('Synthetic (New Type - Soft)', '2'),
('Synthetic  (Old Type - Hard)', '3'),
('Gravel', '5'),
('Clay', '6'),
('Sand', '7'),
('Grass/Synthetic Mix', '8');

Using this structure, the search becomes simpler:

SELECT * FROM field_options WHERE value = '1';

This approach removes the need for complex JSON queries, making data retrieval more straightforward.

The above is the detailed content of How to Efficiently Retrieve JSON Objects in MySQL Based on Key Values?. For more information, please follow other related articles on the PHP Chinese website!