如何在MySQL中筛选JSON_ARRAYAGG生成的convenients数组元素？

问题背景 #

你现有如下SQL语句，通过子查询的JSON_ARRAYAGG生成了名为convenients的JSON数组列：

SELECT 
    h.id,
    h.name,
    h.address,
    JSON_OBJECT(
    "lat", h.latitude,
    "lng", h.longitude) coordinate,
    h.price,
    h.guest_max guestMax,
    h.bedrooms,
    h.beds,
    h.bathrooms,
    h.thumbnail_url thumbNailUrl,
    h.user_id,
    h.area_id,
    ha.name areaName,
    (SELECT JSON_ARRAYAGG(c.name) FROM hotel_convenient hc INNER JOIN convenients c ON c.id = hc.convenient_id WHERE hc.hotel_id = h.id) convenients
  FROM hotels h
  INNER JOIN hotel_areas ha ON h.area_id = ha.id 
  INNER JOIN hotel_convenient hc ON hc.hotel_id = h.id
  INNER JOIN convenients c ON hc.convenient_id = c.id
  GROUP BY h.id
  ORDER BY h.id ASC;

该语句生成的convenients列格式为：

["foo", "bar", "baz"]

需要实现类似JavaScript中Array.prototype.includes()的功能，筛选出convenients数组包含指定元素（比如'foo'、'bar'）的记录。

解决方案 #

方法1：使用JSON_CONTAINS函数 #

MySQL内置的JSON_CONTAINS函数可直接判断JSON数组是否包含指定元素，语法为JSON_CONTAINS(target_json, search_value)。将其加入查询的WHERE子句即可：

SELECT 
    h.id,
    h.name,
    h.address,
    JSON_OBJECT(
    "lat", h.latitude,
    "lng", h.longitude) coordinate,
    h.price,
    h.guest_max guestMax,
    h.bedrooms,
    h.beds,
    h.bathrooms,
    h.thumbnail_url thumbNailUrl,
    h.user_id,
    h.area_id,
    ha.name areaName,
    (SELECT JSON_ARRAYAGG(c.name) FROM hotel_convenient hc INNER JOIN convenients c ON c.id = hc.convenient_id WHERE hc.hotel_id = h.id) convenients
  FROM hotels h
  INNER JOIN hotel_areas ha ON h.area_id = ha.id 
  INNER JOIN hotel_convenient hc ON hc.hotel_id = h.id
  INNER JOIN convenients c ON hc.convenient_id = c.id
  WHERE JSON_CONTAINS(
      (SELECT JSON_ARRAYAGG(c.name) FROM hotel_convenient hc INNER JOIN convenients c ON c.id = hc.convenient_id WHERE hc.hotel_id = h.id),
      '"foo"' -- JSON字符串需用双引号包裹
  )
  GROUP BY h.id
  ORDER BY h.id ASC;

• 若需同时包含多个元素（逻辑与），叠加多个JSON_CONTAINS用AND连接
• 若需包含任意一个元素（逻辑或），用OR连接多个JSON_CONTAINS

方法2：优化查询，避免重复子查询 #

原查询主语句中的INNER JOIN hotel_convenient和INNER JOIN convenients属于冗余关联，可通过EXISTS子句直接从关联表筛选，效率更高：

SELECT 
    h.id,
    h.name,
    h.address,
    JSON_OBJECT("lat", h.latitude, "lng", h.longitude) coordinate,
    h.price,
    h.guest_max guestMax,
    h.bedrooms,
    h.beds,
    h.bathrooms,
    h.thumbnail_url thumbNailUrl,
    h.user_id,
    h.area_id,
    ha.name areaName,
    (SELECT JSON_ARRAYAGG(c.name) 
     FROM hotel_convenient hc 
     INNER JOIN convenients c ON c.id = hc.convenient_id 
     WHERE hc.hotel_id = h.id) convenients
FROM hotels h
INNER JOIN hotel_areas ha ON h.area_id = ha.id 
WHERE EXISTS (
    SELECT 1 
    FROM hotel_convenient hc 
    INNER JOIN convenients c ON c.id = hc.convenient_id 
    WHERE hc.hotel_id = h.id 
    AND c.name IN ('foo', 'bar')
)
GROUP BY h.id
ORDER BY h.id ASC;

这种方式无需处理JSON格式字符串，直接通过关联表筛选，避免了JSON函数的额外开销。

方法3：使用JSON_SEARCH函数 #

JSON_SEARCH会返回指定元素在JSON数组中的路径，若返回非NULL则说明包含该元素：

WHERE JSON_SEARCH(
    (SELECT JSON_ARRAYAGG(c.name) FROM hotel_convenient hc INNER JOIN convenients c ON c.id = hc.convenient_id WHERE hc.hotel_id = h.id),
    'one', -- 'one'匹配第一个结果，'all'匹配所有结果
    'foo'
) IS NOT NULL

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