我正在嘗試找到一種方法來搜尋 JSON 物件並獲取特定鍵,但搜尋另一個鍵。
這是一個範例架構:
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; 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"}]' );
資料庫小提琴:https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1
所以我想要的是透過提供 ID 1
從 options
選擇字串「Grass」。
但似乎沒有辦法做到這一點。我可以通過這樣做獲得草:
select JSON_EXTRACT(`options`, '$[0].text') from `fields`; // "Grass"
但這需要知道數組的索引
我可以像這樣部分取得數組的索引:
select JSON_SEARCH(`options`, 'one', '1') from `fields`; // "$[0].value"
並透過像這樣非常可怕的事情來獲取索引本身:
select REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', '') from `fields`; // 0
甚至透過像這樣非常可怕的事情來實現我想要的:
select JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text')) from `fields`; // "Grass"
但是一定有更好的方法吧?
資料庫小提琴:https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1
P粉5855417662023-10-31 12:44:25
MySQL 8.0提供了JSON_TABLE() 幫助處理這類案件。
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 | +-------+-------+
但是每次您想要寫這樣的查詢時都必須執行這個複雜的 JSON_TABLE() 表達式。
不使用 JSON 會更簡單,而是將資料儲存在具有普通列的表中(每個文字/值對一行)。然後您可以在任一列中搜尋所需的值。
SELECT * FROM field_options WHERE value = '1';
我在 Stack Overflow 上看到的 MySQL 中 JSON 的使用中有 99% 可以透過不使用 JSON 輕鬆解決。