-1

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"]'
July
  • 67
  • 7

1 Answers1

1
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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • i have MySQL 8.0, and i get syntax error on JSON_TABLE – July Oct 25 '22 at 15:48
  • I did test the query I posted, and it has no error. What does `SELECT VERSION();` return in your environment? You may have the 8.0 client but it's the server that's important. – Bill Karwin Oct 25 '22 at 15:53
  • yeah my bad - the MySQL version is 5.7.12 – July Oct 25 '22 at 15:58
  • the idea behind storing the values as a JSON array, is that table B is a temporary table, the insert query for inserting each number per row is too expensive for a big amount of numbers – July Oct 26 '22 at 09:24
  • Im getting an error - "Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' " is the JSON_TABLE creating a table with collation `utf8mb4_general_ci`? if so, is there a way to change it ? – July Oct 26 '22 at 10:09
  • No, it should use whatever your session's `collation_connection` option is set to. You can specify the collation of the column defined in the JSON_TABLE(), I'll add that to the example above. – Bill Karwin Oct 26 '22 at 15:36
  • Or you can run `SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;` as an SQL statement in your session before you run the query. This will set the connection options. – Bill Karwin Oct 26 '22 at 15:37