MySQL JSON反向​​搜索 [英] MySql json reverse search

问题描述

我有一个MySQL表，列的类型为json.此列的值是json array而不是json object.我需要查找此表的记录，这些记录的json列中至少有一个值是给定字符串/短语的子字符串. 假设表看起来像这样:

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"]');

如果输入的字符串/短语是"acf ghi z"，则返回"0".第二列必须作为结果返回，因为"gh"表示是输入的子字符串.我阅读了很多有关json_contains，json_extract，json_search甚至json_overlaps的信息，但无法解决此问题.
检索相关行的正确SQL语法是什么?

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版本是8.0.20

MySQL version is 8.0.20

推荐答案

您可以使用json_table()将JSON数组提取为表中的行.然后进行过滤:

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, '%');

此处是db小提琴.

这篇关于MySQL JSON反向​​搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！