Mysql对JSON_TABLE值的条件

Question

我得到了一个ID列表，作为逗号分隔的JSON值列表，一些示例数据集如下所示: 340596,340597,340595这个列表可能很大，有时是50k ID，由逗号分隔

下面的查询将这些in连接到表主键，并获取表中当前存在的记录

SELECT s.id,s.contactid, s.Quantity FROM 
JSON_TABLE('[340596,340597,340595]', '$[*]' columns (Id int path '$')) AS sm   
LEFT JOIN mastertable s ON s.Id = sm.id  

主程序表可能包含这些ID，也可能是从主程序表中删除的这些记录，因此此查询的目的是确保返回的结果集只包含活动记录。

我必须对这个查询再应用一个筛选，过滤是基于另一个JSON数组，并且需要将它与列ContactID匹配

SELECT s.id,s.contactid, s.Quantity FROM 
JSON_TABLE('[340596,340597,340595]', '$[*]' columns (Id int path '$')) AS sm   
LEFT JOIN mastertable s ON s.Id = sm.id  
WHERE s.ContactId IN (
SELECT cm.id  FROM 
JSON_TABLE('[12345,450597,640595]', '$[*]' columns (Id int path '$')) AS cm  
)

然而，Mysql的性能对大的结果集并不好。我们能用其他更好的方法来代替这个吗？

Answer 1

您可以在临时表中转储IN子句中的ids，然后将它们与JSON_TABLE连接以获得结果。ALternatively您可以使用CTE并加入相同的。

with temp as (
SELECT cm.id  FROM 
JSON_TABLE('[12345,450597,640595]', '$[*]' columns (Id int path '$')) AS cm  
)
SELECT s.id,s.contactid, s.Quantity FROM 
JSON_TABLE('[340596,340597,340595]', '$[*]' columns (Id int path '$')) AS sm   
LEFT JOIN mastertable s ON s.Id = sm.id
INNER JOIN temp t ON s.ID = t.id;