[SOLVED] How to use wildcard search by json column type in MySQL

How to use wildcard search by json column type in MySQL

I got three data in MySQL

[{"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"}]

I need to search data which include "key" = "field_1" and "value" like "%est%", all I know is only json_search have wildcard function, so I've tried:

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

Three of the data were found, I know the problem is that I need to use json_search on the data which includes "key": "filed_1", or it will search all data and find the result which is matched, is there any function to make it?

Solution

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