CODEWITHSUNDEEP
mysqlsqljson
user2041057
user2041057

Reputation: 69

MySql json reverse search

I have a MySQL table with a column of type json. The values of this columns are json array not json object. I need to find records of this table that at least one value of their json column is substring of the given string/phrase. Let's suppose the table is looks like this:

create table if not exists test(id int, col json);
insert into test values (1, '["ab", "cd"]');
insert into test values (2, '["ef", "gh", "ij"]');
insert into test values (3, '["xyz"]');

If the input string/phrase is "acf ghi z" the second column must be returned as the result, because "gh" is a substring of the input. I read a lot about json_contains, json_extract, json_search and even json_overlaps but couldn't manage to solve this problem.
What is the correct sql syntax to retrieve the related rows?

MySQL version is 8.0.20

Upvotes: 1

Views: 606

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use json_table() to extract the JSON array as rows in a table. Then just filter:

select *
from test t cross join
     json_table(t.col, '$[*]' columns (str varchar(255) path '$')) j
where 'acf ghi z' like concat('%', j.str, '%');

Here is a db<>fiddle.

Upvotes: 2

Related Questions