Jasmin Miftari Jasmin Miftari - 2 months ago 12
JSON Question

PostgreSQL Array inside a JSON object

Im trying to build a custom json, from two tables with left join on a PostgreSQL function, but cant it working.

Table Structure:

post

id | title | description


postPhoto

id | postId (FK) | name


json example:

{
postId: 1,
title: ''
description: '',
postPhotos: [
{id: 1, name: 'photo1.png'},
{id: 1, name: 'photo2.png'},
]
}


SQL Function:

$BODY$DECLARE
success boolean;
msg text;
var_most_recent json;
BEGIN


SELECT to_json(array_agg(t)) INTO var_most_recent FROM (

SELECT *
( SELECT to_json(array_agg(t)) FROM (
SELECT * FROM postPhoto AS PP WHERE post.id = PP."postId"
)t )
FROM post
ORDER BY id DESC LIMIT 10

)t;

success = TRUE; msg = 'Successfully Returned Post data';
RETURN (SELECT json_build_object(
'success', success,
'msg', msg,
'body', json_build_object(
'mostRecent', var_most_recent
)
));

END;$BODY$

Answer

I think you are missing the json_agg aggregate function. Here's how I would do it:

SELECT json_agg(p)
FROM
   (SELECT
        json_build_object(
            'postId', post.id,
            'title', post.title,
            'description', post.description,
            'postPhotos',
                (SELECT json_agg(json_build_object('id',id,'name',name))
                 FROM postPhoto WHERE postId post.id)
        ) AS p
    FROM post
    LIMIT 10) as t
)