如何根據鍵值對從 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>

帶有範例資料：

帶有範例資料：

帶有範例資料：

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

問題：

假設我們要從選項JSON 數組中檢索字串“Grass”，知道其對應的“值”是“ 1.”單獨使用JSON_EXTRACT() 需要數組的索引，這可能不容易獲得。

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

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

這種方法結合JSON_SEARCH() 來部分查找索引，然後從字串中刪除不需要的部分：

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

解決方案2：使用JSON_TABLE()MySQL 8.0 引入了JSON_TABLE () 來簡化此任務：替代方法：關聯式資料模型對於像本範例這樣的更簡單的資料結構，請考慮將資料儲存在關係表中包含鍵和值對的列，允許直接搜尋：

以上是如何根據鍵值對從 MySQL 中的 JSON 列中檢索特定物件？的詳細內容。更多資訊請關注PHP中文網其他相關文章！