如何在mysql表中搜索json数组字段中的where字段?

piok6c0g  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(362)

我有一个带有json数组字段的表,我想要一个查询,它根据这个字段从另一个表返回结果
表a

+----+-------------+
| id | interestIds |
+----+-------------+
|  1 | [1, 2, 6, 7]|
+----+-------------+

表\u b

+----+-------------+
| id | country     |
+----+-------------+
|  1 | Ghana       |
|  2 | Grenada     |
|  3 | Jordan      |
|  4 | Latvia      |
|  5 | Malawi      |
|  6 | Mexico      |
|  7 | Moldova     |
+----+-------------+

我想问一下

select * from table_b where id in (select interestIds from table_a where id = 1)
66bbxpm5

66bbxpm51#

你可以用 json_search() :

select b.*
from table_b
where exists (
    select 1 from table_a a where json_search(a.interestIds, 'one', b.id) is not null
)

相关问题