Oracle如何通过用户ID查询地址 表结构验证及嵌套JSON输出SQL实现

你设计的表结构整体是合理的，符合数据库第三范式设计要求：

• 将用户基础属性和多值的地址属性拆分存储，避免了表字段冗余，后续如果需要新增地址类型也可以直接扩展ADDRESS_TYPE的取值，不需要修改表结构
• 通过ADDRESS_TYPE字段区分两类地址，逻辑清晰，完全匹配你每个用户仅存两类地址的业务规则

可以补充3个小优化点，提升数据一致性和可维护性：

1. 给USER_ADDRESS表新增联合唯一约束 (USERNAME, ADDRESS_TYPE)，避免同一个用户的同类型地址重复插入脏数据
2. 你当前的USER_TABLE中缺失目标JSON里的cif字段，需要补充该字段才能正常输出对应值
3. 可给USER_ADDRESS新增独立主键字段（如ADDRESS_ID），方便后续单条地址数据的修改、删除操作

Oracle 12c开始内置了原生JSON生成函数，可以直接通过SQL嵌套生成要求的JSON结构，示例SQL如下：

SELECT JSON_OBJECT(
         'data' VALUE JSON_OBJECT(
           'user' VALUE JSON_OBJECT(
             'cif' VALUE u.cif,
             'username' VALUE u.username,
             'fname' VALUE u.fname,
             'lname' VALUE u.lname,
             'contactDetails' VALUE JSON_OBJECT(
               'permanentAddress' VALUE JSON_OBJECT(
                 'Line1' VALUE MAX(CASE WHEN a.address_type = 'PERMANENT' THEN a.line1 END),
                 'Line2' VALUE MAX(CASE WHEN a.address_type = 'PERMANENT' THEN a.line2 END),
                 'city' VALUE MAX(CASE WHEN a.address_type = 'PERMANENT' THEN a.city END)
               ),
               'correspondenceAddress' VALUE JSON_OBJECT(
                 'Line1' VALUE MAX(CASE WHEN a.address_type = 'CORRESPONDENSE' THEN a.line1 END),
                 'Line2' VALUE MAX(CASE WHEN a.address_type = 'CORRESPONDENSE' THEN a.line2 END),
                 'city' VALUE MAX(CASE WHEN a.address_type = 'CORRESPONDENSE' THEN a.city END)
               ),
               'mobile' VALUE u.mobile,
               'email' VALUE u.email
             )
           )
         )
       ) AS user_json
FROM USER_TABLE u
LEFT JOIN USER_ADDRESS a ON u.username = a.username
WHERE u.username = :input_username -- 此处替换为你要查询的用户ID参数，例如'user_00002'
GROUP BY u.cif, u.username, u.fname, u.lname, u.mobile, u.email;

逻辑说明 #

• 用LEFT JOIN关联用户表和地址表，确保即使用户缺失某类地址也能正常返回结果，不会漏查用户
• 通过CASE WHEN按地址类型分别取值，配合MAX聚合函数将两行地址数据聚合为一行输出
• 嵌套使用JSON_OBJECT逐层生成要求的JSON结构，输出字段名完全匹配你需要的格式

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