如何在MySQL中对比JSON数组列与字符串列，查询差异值

要解决这个问题，核心是先把Table B中JSON格式的电话号码数组拆分成单独的行记录，再和Table A做对比筛选。不同数据库的实现方式略有差异，以下是主流数据库的解决方案：

MySQL 实现 #

使用JSON_TABLE函数将JSON数组拆分为多行，再通过左连接筛选出Table A中不存在的号码：

SELECT t.phone
FROM TableB b
JOIN JSON_TABLE(
    b.number,
    '$[*]' COLUMNS (phone VARCHAR(20) PATH '$')
) t
LEFT JOIN TableA a ON a.phoneNumber = t.phone
WHERE a.phoneNumber IS NULL;

PostgreSQL 实现 #

通过jsonb_array_elements_text（若字段是json类型则用json_array_elements_text）展开JSON数组，再对比筛选：

SELECT t.phone
FROM TableB b,
     jsonb_array_elements_text(b.number::jsonb) AS t(phone)
LEFT JOIN TableA a ON a.phoneNumber = t.phone
WHERE a.phoneNumber IS NULL;

SQL Server 实现 #

利用OPENJSON解析JSON数组，结合CROSS APPLY生成多行记录，再做对比：

SELECT t.phone
FROM TableB b
CROSS APPLY OPENJSON(b.number)
WITH (phone VARCHAR(20) '$') t
LEFT JOIN TableA a ON a.phoneNumber = t.phone
WHERE a.phoneNumber IS NULL;

补充说明 #

你之前用的JSON_EXTRACT只能提取整个数组或指定索引的单个元素，无法直接将数组拆分为多行记录，所以必须搭配数组转行的函数才能完成后续的对比筛选。

内容的提问来源于stack exchange，提问作者July