PostgreSQL Array inside a JSON object

Question

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$

redneb · Accepted Answer · 2016-09-18 17:30:06Z

2

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
    ORDER BY post.id
    LIMIT 10) as t
WHERE json_array_length(p->'postPhotos')>1;

edited Sep 18, 2016 at 17:30

answered Sep 18, 2016 at 16:08

redneb

24.4k6 gold badges50 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jazzy Over a year ago

This works but i need to wrap multiple posts in an array, cause i dont have a where i have a LIMIT by 10 so the json would look like this: pastebin.com/W3MSm8fh

redneb Over a year ago

No problem, we can just wrap it in another SELECT query, see my updated answer.

Jazzy Over a year ago

Can you help me out to select post where postphoto is not null

redneb Over a year ago

@JasminMiftari What can be null exactly?

Jazzy Over a year ago

So for the moment, i select all posts with their postPhotos, but i want to select all posts where postPhotos count > 1. Select post which countain postPhotos ( or contain rows in postPhoto )

|

Collectives™ on Stack Overflow

PostgreSQL Array inside a JSON object

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related