compare two MySQL tables with different types of data - JSON string array vs strings

Table A has one column 'phoneNumber' type string, Table B with column 'number' type JSON. table B has one record with the value json array of phoneNumbers: '["052669988", "0526635444"]'. what is the best way to select all the values that Exist in table B but not Exist in table A? I tried to extract the values with JSON_EXTRACT() - but how can i use them ?

table A:

phoneNumber
"052111111"

table B:

number
'["052669988", "0526635444"]'

Solution

SELECT n.number
FROM tableB AS b
CROSS JOIN JSON_TABLE(b.number, '$[*]' COLUMNS(
    number CHAR(10) COLLATE utf8mb4_0900_ai_ci PATH '$'
  )
) AS n
LEFT OUTER JOIN tableA AS a ON n.number = a.PhoneNumber
WHERE a.id IS NULL;

JSON_TABLE() requires MySQL 8.0.

Since you commented that you are using MySQL 5.7, you can't use the solution above. You can either upgrade to 8.0 (fyi, 5.7 is end-of-life in October 2023, so you should be starting to think about upgrading before then anyway), or else find another solution.

What I would recommend is to not store data in JSON arrays.

If you were to store one phone number per row, then the solution would just be a simple outer join, comparing the number in one table to the number in the other table, and selecting where the other table is NULL because of the outer join.

SELECT b.number
FROM tableB AS b
LEFT OUTER JOIN tableA AS a ON b.number = a.PhoneNumber
WHERE a.id IS NULL;

This solution works in any version of MySQL and it's easier to read and easier to optimize.