Return JSON in Postgres

Question

I have created a function in postgres that returns json.

The function is used in an API and returns this:

[
    {
        "jobfeed_beroepen_list": [
            {
                "profession_code": 3674,
                "profession_descr": "Manusje van alles (boodschappen, post en goederen)"
            },
            {
                "profession_code": 4107,
                "profession_descr": "Algemeen medewerker"
            }
        ]
    }
]

But I want it to return this:

[
    {
        "profession_code": 3674,
        "profession_descr": "Manusje van alles (boodschappen, post en goederen)"
    },
    {
        "profession_code": 4107,
        "profession_descr": "Algemeen medewerker"
    }
]

Question: what do I have to change in the code?

--------------- this is the function --------------

CREATE OR REPLACE FUNCTION api.fnc_jobfeed_beroepen(
    )
    RETURNS TABLE(jobfeed_beroepen_list jsonb) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
DECLARE

BEGIN
jobfeed_beroepen_list := to_jsonb(array_agg(t)) FROM
                    (SELECT profession_code, profession_descr
                    FROM skillcv.jobfeed_beroepen
                    WHERE use = 1
                    ) AS t;
RETURN QUERY SELECT jobfeed_beroepen_list;

END;
$BODY$;

score 1 · Accepted Answer · answered Nov 20 '20 at 12:43

1

You can use json aggregation. That would be:

return query 
    select jsonb_agg(
        jsonb_build_object(
            'profession_code',  profession_code, 
            'profession_descr', profession_descr
        )
    ) as res
    from skillcv.jobfeed_beroepen
    where use = 1

answered Nov 20 '20 at 12:43

GMB

216,147
25
84
135

Thanks. Would this replace the part between BEGIN and END; in the original function? – Arjan Ruis Nov 20 '20 at 13:46
@ArjanRuis: yes, exactly. – GMB Nov 20 '20 at 13:48
What do I put in the RETURN of the function? Initially this was "RETURNS TABLE(jobfeed_beroepen_list jsonb)", but that is I want to avoid. – Arjan Ruis Nov 20 '20 at 14:38

score 0 · Answer 2 · edited Dec 07 '21 at 15:06

CREATE OR REPLACE FUNCTION api.fnc_jobfeed_beroepen()
    RETURNS TABLE(jobfeed_beroepen_list jsonb) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
DECLARE

begin
    return query(select jsonb_agg(
        jsonb_build_object(
            'profession_code', profession_code, 
            'profession_descr', profession_descr
        )
    ) as res
    from skillcv.jobfeed_beroepen
    where use = 1)

end;
$BODY$;

Return JSON in Postgres

2 Answers2