如何让MySQL返回包含内嵌图片路径数组的JSON格式商品查询结果

• 当前你编写的查询仅读取了product单表数据，没有关联存储图片路径的product_images表，无法拿到商品对应的三张图片地址，自然没法组装成你需要的JSON结构
• 你当前的表结构属于固定3张图片的强绑定设计，如果后续业务有调整图片数量的需求，改动成本较高，如果确定业务永远要求每个商品对应3张图可以继续沿用

因为你用了PostgreSQL的serial字段类型，可直接用数组函数拼接图片路径，查询语句如下：

SELECT 
  p.product_id,
  p.product_name,
  p.category_id_fk,
  p.product_price,
  p.product_desc,
  ARRAY[pi.product_image_one, pi.product_image_two, pi.product_image_three] AS product_images
FROM product p
INNER JOIN product_images pi ON p.product_image_fk = pi.product_images_id
WHERE p.category_id_fk = ? AND p.product_id = ?;

查询结果里的product_images已经是数组格式，后端直接序列化即可得到你要求的JSON结构。如果需要直接在数据库层面返回符合要求的JSON，可以用以下写法：

SELECT json_build_object(
  'product_id', p.product_id,
  'product_name', p.product_name,
  'category_id_fk', p.category_id_fk,
  'product_price', p.product_price,
  'product_desc', p.product_desc,
  'product_images', ARRAY[pi.product_image_one, pi.product_image_two, pi.product_image_three]
) AS product_info
FROM product p
INNER JOIN product_images pi ON p.product_image_fk = pi.product_images_id
WHERE p.category_id_fk = ? AND p.product_id = ?;

如果后续有调整商品图片数量的可能，建议改成一对多的通用设计，调整后的表结构如下：

-- 移除product表的图片外键字段
create table product(
product_id serial not null primary key,
category_id_fk int not null references category(category_id) ,
product_name varchar(50) not null,
product_price int not null,
product_desc varchar(200) not null);

-- 图片表新增商品外键，单条数据存一张图片路径
create table product_images(
product_images_id serial not null primary key,
product_id_fk int not null references product(product_id),
image_path varchar(150) not null
);

调整后查询语句更灵活，不受图片数量限制：

SELECT json_build_object(
  'product_id', p.product_id,
  'product_name', p.product_name,
  'category_id_fk', p.category_id_fk,
  'product_price', p.product_price,
  'product_desc', p.product_desc,
  'product_images', json_agg(pi.image_path)
) AS product_info
FROM product p
INNER JOIN product_images pi ON p.product_id = pi.product_id_fk
WHERE p.category_id_fk = ? AND p.product_id = ?
GROUP BY p.product_id;

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