Postgres jsonb_build_object return empty instead null for empty row

Question

I am trying to create JSON output using Postgres.

I am close to the solution I think.

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

My output is close to the solution. Please see below. The only problem is that the empty array are presented with [null] instead of []. How can I do this.

mayu · Accepted Answer · 2021-08-03 13:43:22Z

I found the solution.

I hope it helps others.

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;

Collectives™ on Stack Overflow

Postgres jsonb_build_object return empty instead null for empty row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related