How to Efficiently Retrieve a Text Value from a JSON Object in MySQL Based on a Key Value?

Retrieving Objects from JSON Keys in MySQL

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!