关于MySQL json_extract()函数的技术咨询：双表关联操作问题

嘿，看起来你是在处理MySQL里JSON字段的查询问题，结合你给出的users表结构，我先给你几个关于json_extract()和两表关联的常见操作示例，你可以参考下，如果有更具体的需求（比如要筛选特定邀请用户的房间、提取特定字段之类的），随时补充哦~

1. 基础提取：从room表的invited字段获取内容 #

假设你的room表invited字段的JSON结构是类似{"emails": ["red@google.com", "green@google.com"]}或者[{"user_id":1, "email":"red@google.com"}]这类，不同结构的提取方式如下：

• 如果是包含邮箱数组的JSON：

-- 提取整个邮箱数组
SELECT id, json_extract(invited, '$.emails') AS invited_emails
FROM room;

-- 提取数组中的第一个邮箱
SELECT id, json_extract(invited, '$.emails[0]') AS first_invited_email
FROM room;

• 如果是包含用户ID数组的JSON：

SELECT id, json_extract(invited, '$.user_ids') AS invited_user_ids
FROM room;

2. 关联users表：匹配被邀请的用户 #

比如你想找出所有邀请了red@google.com的房间，同时关联显示该用户的信息，可以这么写：

SELECT r.id AS room_id, u.id AS user_id, u.email
FROM room r
JOIN users u 
  ON json_contains(r.invited, JSON_QUOTE(u.email), '$.emails')
WHERE u.email = 'red@google.com';

要是你需要批量匹配所有被邀请的用户和对应的房间，还可以用JSON_TABLE把JSON数组转成临时表再关联：

SELECT r.id AS room_id, u.id AS user_id, u.email
FROM room r
JOIN JSON_TABLE(
    r.invited,
    '$.emails[*]' COLUMNS(invited_email VARCHAR(255) PATH '$')
) AS extracted_emails
JOIN users u ON u.email = extracted_emails.invited_email;

3. 统计类操作：计算每个房间的邀请人数 #

结合json_length和json_extract，可以快速统计每个房间的邀请人数：

SELECT id AS room_id, 
       json_length(json_extract(invited, '$.emails')) AS total_invited
FROM room;

内容的提问来源于stack exchange，提问作者K.min