如何通过单查询从DynamoDB两张关联表获取嵌套结构数据？

1. 原生SQL实现（按数据库类型区分） #

PostgreSQL #

使用json_agg()函数聚合关联数据为JSON数组：

SELECT
    e."#Emp-PK" AS "PK",
    e."#Emp-SK" AS "SK",
    e."Name" AS "name",
    json_agg(
        json_build_object(
            'PK', ep."#Emp-PRO-PK",
            'SK', ep."#Emp-PRO-SK",
            'project-title', ep."project-title"
        )
    ) AS "projects"
FROM "Employee" e
LEFT JOIN "Employee-projects" ep 
    ON e."#Emp-PK" = ep."EmpPKKey" 
    AND e."#Emp-SK" = ep."EmpSKKey"
GROUP BY e."#Emp-PK", e."#Emp-SK", e."Name";

MySQL #

通过JSON_ARRAYAGG()和JSON_OBJECT()组合生成嵌套结构：

SELECT
    e.`#Emp-PK` AS `PK`,
    e.`#Emp-SK` AS `SK`,
    e.`Name` AS `name`,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'PK', ep.`#Emp-PRO-PK`,
            'SK', ep.`#Emp-PRO-SK`,
            'project-title', ep.`project-title`
        )
    ) AS `projects`
FROM `Employee` e
LEFT JOIN `Employee-projects` ep 
    ON e.`#Emp-PK` = ep.`EmpPKKey` 
    AND e.`#Emp-SK` = ep.`EmpSKKey`
GROUP BY e.`#Emp-PK`, e.`#Emp-SK`, e.`Name`;

SQL Server #

用STRING_AGG()拼接JSON字符串再转为数组：

SELECT
    e.[#Emp-PK] AS [PK],
    e.[#Emp-SK] AS [SK],
    e.[Name] AS [name],
    JSON_QUERY('[' + STRING_AGG(
        JSON_QUERY(
            CONCAT(
                '{"PK":"', ep.[#Emp-PRO-PK], '",',
                '"SK":"', ep.[#Emp-PRO-SK], '",',
                '"project-title":"', ep.[project-title], '"}'
            )
        ), ','
    ) + ']') AS [projects]
FROM [Employee] e
LEFT JOIN [Employee-projects] ep 
    ON e.[#Emp-PK] = ep.[EmpPKKey] 
    AND e.[#Emp-SK] = ep.[EmpSKKey]
GROUP BY e.[#Emp-PK], e.[#Emp-SK], e.[Name];

2. ORM框架实现（以MyBatis为例） #

无需手动拼接复杂SQL，通过结果映射直接生成嵌套对象：

<resultMap id="EmployeeResultMap" type="Employee">
    <id property="PK" column="#Emp-PK"/>
    <result property="SK" column="#Emp-SK"/>
    <result property="name" column="Name"/>
    <collection property="projects" ofType="EmployeeProject">
        <id property="PK" column="#Emp-PRO-PK"/>
        <result property="SK" column="#Emp-PRO-SK"/>
        <result property="projectTitle" column="project-title"/>
    </collection>
</resultMap>

<select id="getEmployeesWithProjects" resultMap="EmployeeResultMap">
    SELECT 
        e.#Emp-PK, e.#Emp-SK, e.Name,
        ep.#Emp-PRO-PK, ep.#Emp-PRO-SK, ep.project-title
    FROM Employee e
    LEFT JOIN Employee-projects ep 
        ON e.#Emp-PK = ep.EmpPKKey 
        AND e.#Emp-SK = ep.EmpSKKey
</select>

执行该查询后，框架会自动将同一员工的项目数据封装到projects集合中。

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