如何根据键值对从 MySQL 中的 JSON 列中检索特定对象？

MySQL JSON：根据键值搜索对象

MySQL 表通常使用 JSON 列来存储复杂的数据结构。使用 JSON 时，一个常见任务是根据键值对检索特定对象。然而，直接查找对象可能并不总是那么简单。

JSON 架构示例：

考虑以下架构：

<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"}, {"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>

问题：

假设我们要从选项 JSON 数组中检索字符串“Grass”，知道其对应的“值”是“ 1.”单独使用 JSON_EXTRACT() 需要数组的索引，这可能不容易获得。

解决方案 1：使用 JSON_SEARCH() 和可怕的字符串操作

这种方法结合 JSON_SEARCH() 来部分查找索引，然后从字符串中删除不需要的部分：

<code class="sql">SELECT
JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text'))
from `fields`;</code>

解决方案 2：使用 JSON_TABLE()

MySQL 8.0 引入了 JSON_TABLE () 来简化此任务：

<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>

替代方法：关系数据模型

对于像本示例这样的更简单的数据结构，请考虑将数据存储在关系表中包含键和值对的列，允许直接搜索：

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

以上是如何根据键值对从 MySQL 中的 JSON 列中检索特定对象？的详细内容。更多信息请关注PHP中文网其他相关文章！