在Oracle中构建包含图片子数组的JSON数据

一、正确的JSON格式 #

假设你的查询结果是同一用户对应多条图片路径（比如id=1的用户有2个图片路径），原始数据如下：

对应的标准JSON格式需要将同一用户的图片路径合并为数组，保证数据结构的统一性，方便后续业务处理：

[
  {
    "id": 1,
    "name": "John",
    "last": "Doe",
    "image_paths": ["/img/1.jpg", "/img/2.jpg"]
  },
  {
    "id": 2,
    "name": "Jane",
    "last": "Smith",
    "image_paths": ["/img/3.jpg"]
  }
]

即使单个用户只有1个图片路径，也建议保持数组格式，避免后续处理时因格式不一致出现异常。

二、实现方式 #

1. 数据库端直接生成JSON #

主流数据库都提供了原生的JSON聚合函数，可以直接在SQL中生成目标格式：

MySQL/MariaDB

用JSON_OBJECT构建单用户对象，JSON_ARRAYAGG聚合图片路径：

-- 生成单条用户的JSON对象
SELECT 
  JSON_OBJECT(
    'id', id,
    'name', name,
    'last', last,
    'image_paths', JSON_ARRAYAGG(image_path)
  ) AS user_json
FROM pictutes
GROUP BY id, name, last;

-- 生成包含所有用户的JSON数组
SELECT JSON_ARRAYAGG(user_json) AS all_users_json
FROM (
  SELECT 
    JSON_OBJECT(
      'id', id,
      'name', name,
      'last', last,
      'image_paths', JSON_ARRAYAGG(image_path)
    ) AS user_json
  FROM pictutes
  GROUP BY id, name, last
) AS subquery;

PostgreSQL

通过json_build_object和json_agg组合实现：

-- 生成单条用户的JSON对象
SELECT 
  json_build_object(
    'id', id,
    'name', name,
    'last', last,
    'image_paths', json_agg(image_path)
  ) AS user_json
FROM pictutes
GROUP BY id, name, last;

-- 生成包含所有用户的JSON数组
SELECT json_agg(user_json) AS all_users_json
FROM (
  SELECT 
    json_build_object(
      'id', id,
      'name', name,
      'last', last,
      'image_paths', json_agg(image_path)
    ) AS user_json
  FROM pictutes
  GROUP BY id, name, last
) AS subquery;

Oracle（12c+）

使用JSON_OBJECT和JSON_ARRAYAGG函数：

-- 生成单条用户的JSON对象
SELECT 
  JSON_OBJECT(
    'id' VALUE id,
    'name' VALUE name,
    'last' VALUE last,
    'image_paths' VALUE JSON_ARRAYAGG(image_path)
  ) AS user_json
FROM pictutes
GROUP BY id, name, last;

-- 生成包含所有用户的JSON数组
SELECT JSON_ARRAYAGG(user_json) AS all_users_json
FROM (
  SELECT 
    JSON_OBJECT(
      'id' VALUE id,
      'name' VALUE name,
      'last' VALUE last,
      'image_paths' VALUE JSON_ARRAYAGG(image_path)
    ) AS user_json
  FROM pictutes
  GROUP BY id, name, last
) AS subquery;

2. 应用端处理（以Python为例） #

如果数据库不支持原生JSON聚合，或者需要更灵活的逻辑，可以在应用层查询原始数据后再构建JSON：

import mysql.connector
import json

# 连接数据库（根据你的数据库类型调整驱动）
conn = mysql.connector.connect(
  host="你的数据库地址",
  user="用户名",
  password="密码",
  database="数据库名"
)
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT id, name, last, image_path FROM pictutes;")
rows = cursor.fetchall()

# 合并同一用户的图片路径
users_map = {}
for row in rows:
    user_id = row["id"]
    if user_id not in users_map:
        users_map[user_id] = {
            "id": user_id,
            "name": row["name"],
            "last": row["last"],
            "image_paths": []
        }
    users_map[user_id]["image_paths"].append(row["image_path"])

# 转换为标准JSON格式
result_json = json.dumps(list(users_map.values()), indent=2)
print(result_json)

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