sql - Postgres jsonb_build_object 为空行返回空而不是空

Question

我正在尝试使用 Postgres 创建 JSON 输出。

我接近我认为的解决方案。

SELECT D.PROJECT_ID,
    D.M_NAME,
    D.M_ID,
    JSON_AGG(D.TASK) AS TASKS
FROM
    (SELECT MILESTONES.ID AS M_ID,
            MILESTONES.NAME AS M_NAME,
            MILESTONES.DUE_DATE,
            PROJECT_ID,
            TASK
        FROM MILESTONES_TABLE MILESTONES
        LEFT JOIN
            (SELECT JSONB_BUILD_OBJECT('name',
                                        ASSIGNMENTS.NAME,
                                        'instructions',
                                        INSTRUCTIONS,
                                        'id',
                                        ASSIGNMENTS.ID) AS TASK,
                    MILESTONE_ID
                FROM ASSIGNMENTS) AS F ON MILESTONE_ID = MILESTONES.ID
        WHERE MILESTONES.PROJECT_ID = 270 ) AS D
GROUP BY D.PROJECT_ID,
    D.M_NAME,
    D.M_ID

我的输出接近解决方案。请看下文。唯一的问题是空数组以[null]代替[]. 我怎样才能做到这一点。

标签： sqlarraysjsonpostgresql

Answer 1

我找到了解决方案。

我希望它可以帮助其他人。

SELECT D.PROJECT_ID,
    D.M_NAME,
    D.M_ID,
     count(d),
     
    COALESCE(NULLIF(json_agg(TASK)::TEXT, '[null]'), '[]')::JSON
FROM
    (SELECT MILESTONES.ID AS M_ID,
            MILESTONES.NAME AS M_NAME,
            MILESTONES.DUE_DATE,
            PROJECT_ID,
            TASK
        FROM MILESTONES_TABLE MILESTONES
        LEFT JOIN
            (SELECT case when count(ASSIGNMENTS.ID) = 0 then '[]' else JSONB_BUILD_OBJECT('name',

                                                ASSIGNMENTS.NAME,
                                                'instructions',
                                                INSTRUCTIONS,
                                                'id',
                                                ASSIGNMENTS.ID) END AS TASK,
                    MILESTONE_ID
                FROM ASSIGNMENTS GROUP BY ASSIGNMENTS.ID) AS F ON MILESTONE_ID = MILESTONES.ID
        WHERE MILESTONES.PROJECT_ID = 270 ) AS D
GROUP BY D.PROJECT_ID,
    D.M_NAME,
    D.M_ID;