mysql - 如何使用SQL检查一个表中存在的json值数组是否存在于另一个表中？

Question

我有两张看起来像这样的桌子..

篮子

ID	大批
1	["Apple", "Mango", "Pineapple"]
2	["Mango", "Pineapple"]
3	["Dragonfruit"]

库存

水果	数数
苹果	100
芒果	500
猕猴桃	99
葡萄	0

通过 SQL 查询，我必须检查第一个表的数组中有多少水果存在于第二个表中。

例如， out of ["Apple", "Mango", "Pineapple"]onlyApple和Mangois 在 Stock 表中。

预期结果：

ID	可用的
1	2
2	1
3	0

PS。这是我试图解决的实际问题的简化，我的主要查询是如何将 JSON 对象数组与其他表进行匹配。水果/库存示例仅用于解释问题。

标签： mysqlsql

Answer 1

SELECT Baskets.id, COUNT(Stock.Fruit) available
FROM Baskets 
LEFT JOIN Stock ON JSON_SEARCH(Baskets.Array, 'one', Stock.Fruit) IS NOT NULL
GROUP BY Baskets.id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a0f8e8fba8851fcda508affca0201ba9