如何查询对象数组匹配指定值？检查variation_id在Combo_ids列的方案

数据表结构 #

现有variation_table表及数据如下：

问题 #

• 如何查询匹配任意给定值的对象数组？
• 已知某个variation_id（例如420），如何检查该值是否存在于variation_table的Combo_ids列中？

已实现的基础方案 #

通过模糊匹配LIKE语句可实现需求，SQL代码如下：

SELECT * FROM variation_table v WHERE v.Combo_ids LIKE '%\"variation_id\":\"420\"%';

该语句可正常执行，但希望找到更适配JSON数组的过滤方案。

优化方案：使用JSON_TABLE #

利用JSON_TABLE函数将JSON数组转换为关系型临时表，再进行精准过滤，SQL代码如下：

SELECT variation_combo.*, v.Combo_ids, v.id FROM variation_table v ,
     JSON_TABLE( v.Combo_ids , '$[*]' COLUMNS (
                variation_id VARCHAR(40)  PATH '$.variation_id',
                quantity float(25) PATH '$.quantity')
                ) variation_combo
                WHERE variation_combo.variation_id = 420;

内容的提问来源于stack exchange，提问作者Prabhath Wijewardhana