Problem:
When working with JSON data in a MySQL database, how can you efficiently search for a specific object based on a key value while querying using a different key?
Schema:
Consider the following example 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>
<code class="sql">INSERT INTO `fields` (label, options) VALUES ( 'My Field', '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, ...]');</code>
Objective:
Given the ID of a field, retrieve the corresponding text value of a specific option based on its value.
Previous Solutions:
Initial attempts involved using a combination of JSON functions. However, these approaches were cumbersome and required complex expressions.
JSON_TABLE() Function (MySQL 8.0):
MySQL 8.0 introduced the JSON_TABLE() function, which simplifies the process:
<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; +-------+-------+ | text | value | +-------+-------+ | Grass | 1 | +-------+-------+</code>
Non-JSON Alternative:
A simpler solution is to convert the JSON data into a normalized table:
<code class="sql">CREATE TABLE `field_options` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `field_id` int(10) unsigned NOT NULL, `text` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `value` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`field_id`) REFERENCES `fields`(`id`) );</code>
<code class="sql">INSERT INTO `field_options` (field_id, text, value) VALUES ( 1, 'Grass', '1'), (1, 'Synthetic (New Type - Soft)', '2'), ... );</code>
This approach allows for efficient lookups using traditional SQL techniques:
<code class="sql">SELECT * FROM `field_options` WHERE value = '1';</code>
The above is the detailed content of How to Efficiently Retrieve a Text Value from a JSON Object in MySQL Based on a Key Value?. For more information, please follow other related articles on the PHP Chinese website!