如何实现表字符串字段与JSON数组多值字段的匹配关联查询

核心逻辑：先将用户偏好的JSON数组拆分为单个城镇的行数据，和房源表的城镇字段关联后，再按用户维度聚合匹配到的房源ID即可，以下是不同常用数据库的实现写法：

MySQL 8.0+ 版本 #

SELECT
  b.ID AS `User ID`,
  b.Name AS `User Name`,
  GROUP_CONCAT(a.ID ORDER BY a.ID SEPARATOR ', ') AS `Matched Property ID`
FROM `User preferences` b
-- 拆解JSON数组为单个城镇行
JOIN JSON_TABLE(
  b.`Preferred Towns`,
  '$[*]' COLUMNS (preferred_town VARCHAR(255) PATH '$')
) AS pref
-- 关联房源表的城镇
JOIN `Property listings` a ON a.Town = pref.preferred_town
GROUP BY b.ID, b.Name;

PostgreSQL 版本 #

SELECT
  b."ID" AS "User ID",
  b."Name" AS "User Name",
  STRING_AGG(a."ID"::TEXT, ', ' ORDER BY a."ID") AS "Matched Property ID"
FROM "User preferences" b
-- 拆解JSON数组为单个城镇行
JOIN LATERAL jsonb_array_elements_text(b."Preferred Towns"::jsonb) AS pref(preferred_town) ON true
-- 关联房源表的城镇
JOIN "Property listings" a ON a."Town" = pref.preferred_town
GROUP BY b."ID", b."Name";

Spark SQL / Hive 版本 #

SELECT
  b.ID AS `User ID`,
  b.Name AS `User Name`,
  CONCAT_WS(', ', COLLECT_LIST(a.ID)) AS `Matched Property ID`
FROM `User preferences` b
-- 先解析JSON数组，再拆解为单行
LATERAL VIEW EXPLODE(FROM_JSON(b.`Preferred Towns`, 'array<string>')) pref AS preferred_town
-- 关联房源表
JOIN `Property listings` a ON a.Town = pref.preferred_town
GROUP BY b.ID, b.Name;

兜底兼容写法（不推荐，仅适用于不支持JSON拆解函数的低版本数据库，存在城镇名互相包含时匹配错误的问题）

SELECT
  b.ID AS `User ID`,
  b.Name AS `User Name`,
  GROUP_CONCAT(a.ID SEPARATOR ', ') AS `Matched Property ID`
FROM `User preferences` b
JOIN `Property listings` a 
  ON JSON_CONTAINS(b.`Preferred Towns`, JSON_QUOTE(a.Town))
GROUP BY b.ID, b.Name

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