Mysql WHERE condition against a JSON

I got a list of IDs as comma seperated list of JSON values and some example data sets are like below [340596,340597,340595] This list can be huge sometimes 50k IDs seperated by commas

The following query connects these IDs to a table primary key and fetch the records that is currently exists in the table

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

The mastertable might contain these IDs or might be these records are erased from mastertable So the purpose of this query is to ensure the return result set contains only the active records

I have to apply one more filtering against this query and the filtering is based on another JSON int array and need to match it against the column 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  
)

However the Mysql IN perfomance is not better for large result sets . Can we replace this IN with some other better ways?

CodePudding user response：

You can dump the ids inside IN clause in a temporary table and then join them with JSON_TABLE to get the result. ALternatively you can use a CTE and join the same.

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;