将JSON数组字段与mysql中的字符串字段连接 [英] Join JSON array field with string field in mysql

问题描述

我正在寻找表2中的记录，这些记录的ID存在于表1中的nums字段(JSON)的值中.

I am looking for records in table 2 whose id exist in the values of nums field (JSON) in table 1.

表1

id | nums (JSON)
---+-------------------
1  | ["1","2","3","4"]
2  | ["7","8","5","6"]
3  | ["9","10","3","4"]

表2

id |
---+
1  | 
2  | 
53 | 
63 | 

我想得到下一个结果.

I would like to get the next result.

需要行

id |
---+
1  | 
2  | 

我正在使用5.7 mysql版本.

I am using 5.7 mysql version.

推荐答案

尝试一下，

SELECT * FROM table1 as t1
    LEFT JOIN table 2 as t2 on JSON_CONTAINS(t1.id->'$[*]', CAST(t2.id as JSON))

这篇关于将JSON数组字段与mysql中的字符串字段连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！