Question 281898 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0 
    SELECT
    metadata
FROM
    form_results
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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search