MySQL json 格式的 wildcard 搜尋問題

mysql json

chan15 2023-11-08 14:52:18 ‧ 644 瀏覽

分享至

資料庫有三筆資料

[{"key": "field_1", "value": "test_1"}, {"key": "field_2", "value": "test_2"}]
[{"key": "field_1", "value": "1"}, {"key": "field_2", "value": "test_2"}]
[{"key": "field_2", "value": "test_2"}, {"key": "field_1", "value": "test_1"}]

我想達到的結果為 "key" = "field_1" and "value" like "%est%"，也就是說只有 1 跟 3 符合，目前查了 MySQL 的 function 只有 json_search 有 wildcard 的功能，目前我卡在：

select *
from `table`
where json_contains(metadata, '[{"key": "field_1"}]') and json_search(metadata, 'one', '%est%', null, '$[*].value') is not null

這樣還是三筆都會找到，要把 $[*].value 裡面的 * 帶到符合 "key": "field_1" 那個 index 才是解答，想請教有沒有比較好的 function 可以做到這件事，謝謝。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

迷路

iT邦新手 1 級 ‧ 2023-11-09 08:47:13

試試看REGEXP

SELECT *
FROM `table`
WHERE `metadata` REGEXP '\"key\"\s?\:\s?\"field_1\"\s?\,\s?\"value\"\s?\:\s?\"[^\"]*est[^\"]*\"';

回應
分享
檢舉

登入發表回應

sum90707

iT邦見習生 ‧ 2023-11-10 17:28:11

SELECT
    metadata
FROM
    t
WHERE
    JSON_CONTAINS(metadata, '[{"key": "field_1"}]')
AND
    JSON_SEARCH(
        metadata,
        'one',
        '%est%',
        null,
        JSON_UNQUOTE(REPLACE(JSON_SEARCH(metadata, 'one', 'field_1', null, '$[*].key'), 'key', 'value'))
    ) IS NOT NULL

針對每個 json array 中的 object做搜尋找到 key = field_1 後拿到 path，再將 path 中的 key 改為 value 再次做搜尋