Home > Database > Mysql Tutorial > body text

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

Patricia Arquette
Release: 2024-11-05 00:19:01
Original
726 people have browsed it

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>
Copy after login
<code class="sql">INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, ...]');</code>
Copy after login

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>
Copy after login

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>
Copy after login
<code class="sql">INSERT INTO `field_options` (field_id, text, value) VALUES
(
  1, 'Grass', '1'),
  (1, 'Synthetic (New Type - Soft)', '2'),
  ...
);</code>
Copy after login

This approach allows for efficient lookups using traditional SQL techniques:

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

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!