CODEWITHSUNDEEP
mysqlmysql-json
Chan
Chan

Reputation: 1969

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?

Upvotes: 0

Views: 136

Answers (2)

HenryLin
HenryLin

Reputation: 36

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

Upvotes: 2

Akina
Akina

Reputation: 42764

CREATE TABLE test (metadata JSON)
SELECT '[{"key": "field_1", "value": "test_1"}, {"key": "field_2", "value": "test_2"}]' metadata
  UNION ALL
SELECT '[{"key": "field_1", "value": "1"}, {"key": "field_2", "value": "test_2"}]'
  UNION ALL
SELECT '[{"key": "field_2", "value": "test_2"}, {"key": "field_1", "value": "test_1"}]';
SELECT CAST(metadata AS CHAR) FROM test;
CAST(metadata AS CHAR)
[{"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"}] 
SELECT CAST(metadata AS CHAR)
FROM test
CROSS JOIN JSON_TABLE(test.metadata,
                      '$[*]' COLUMNS (`key` TEXT PATH '$.key',
                                      `value` TEXT PATH '$.value')) jsontable
WHERE jsontable.key = 'field_1'
  AND jsontable.value LIKE '%est%';
CAST(metadata AS CHAR)
[{"key": "field_1", "value": "test_1"}, {"key": "field_2", "value": "test_2"}]
[{"key": "field_2", "value": "test_2"}, {"key": "field_1", "value": "test_1"}]

fiddle

Upvotes: 2

Related Questions