如何在单个JSON中实现双查询？多表全量查询JSON输出方案咨询

嘿，我来帮你梳理下这个问题～针对你要从两张表查询数据并生成JSON，以及是否能在单个JSON里包含两个查询的需求，我给你整理了具体的解决方案：

首先结合你的表结构，我们可以用SQL的关联查询+JSON函数来生成符合需求的JSON结果。先说明下，虽然你提到知晓SQL注入风险仅作示例，但实际生产环境一定要用参数化查询，绝对不能直接拼接用户输入哦！

方案1：生成每个用户的嵌套JSON（关联用户与对应posts） #

如果你想让每个用户的基本信息和他们的posts在同一个JSON对象里，用MySQL的话可以这么写：

SELECT 
  JSON_OBJECT(
    'user_info', JSON_OBJECT(
      'id', u.id,
      'username', u.username,
      'profilepic', u.profilepic
    ),
    'user_posts', JSON_ARRAYAGG(ui.posts)
  ) AS single_user_data
FROM users u
INNER JOIN user_images ui ON u.username = ui.username
GROUP BY u.id, u.username, u.profilepic;

执行后会返回两条结果，每条对应一个用户的完整数据，比如Bill的结果是：

{
  "user_info": {"id": 1, "username": "Bill", "profilepic": "image.png"},
  "user_posts": ["Food", "Sports"]
}

方案2：生成包含所有用户的JSON数组 #

如果想要把所有用户的数据打包成一个JSON数组，可以在外层再加一层JSON_ARRAYAGG：

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'user_info', JSON_OBJECT(
      'id', u.id,
      'username', u.username,
      'profilepic', u.profilepic
    ),
    'user_posts', JSON_ARRAYAGG(ui.posts)
  )
) AS all_users_combined_data
FROM users u
INNER JOIN user_images ui ON u.username = ui.username
GROUP BY u.id, u.username, u.profilepic;

结果会是一个包含两个用户对象的JSON数组，方便一次性获取所有数据。

你完全可以把两个独立查询的结果合并到一个JSON对象里，比如分别查询users表和user_images表的全部数据，然后包装成不同的节点。还是以MySQL为例：

SELECT JSON_OBJECT(
  'full_users_list', (
    SELECT JSON_ARRAYAGG(
      JSON_OBJECT('id', id, 'username', username, 'profilepic', profilepic)
    ) FROM users
  ),
  'full_user_images_list', (
    SELECT JSON_ARRAYAGG(
      JSON_OBJECT('id', id, 'username', username, 'posts', posts)
    ) FROM user_images
  )
) AS combined_full_data;

这个查询会生成一个包含两个键的大JSON对象，full_users_list对应users表的所有数据数组，full_user_images_list对应user_images表的所有数据数组，结果示例：

{
  "full_users_list": [
    {"id":1,"username":"Bill","profilepic":"image.png"},
    {"id":2,"username":"Sally","profilepic":"cats.png"}
  ],
  "full_user_images_list": [
    {"id":1,"username":"Bill","posts":"Food"},
    {"id":2,"username":"Bill","posts":"Sports"},
    {"id":3,"username":"Sally","posts":"Coffee"}
  ]
}

要是用的是PostgreSQL这类数据库，语法会有小差别（比如用json_agg代替JSON_ARRAYAGG），但核心思路都是用JSON函数把不同查询的结果包装成不同的JSON节点，再组合成一个整体。

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