PostgreSql format jsonb array

Question

Can not find a proper query to format jsonb output in postgres I have a jonb column in a table.

Table: Users
id| posts
--------
1 | [{'title': '', 'is_published': '', 'description': '', 'some': 'extra'}, ...]
2 | [{'title': '', 'is_published': '', 'description': '', 'some': 'extra'}, ...]

How do I select the posts column to have just certain fields? Something like:

id| posts
--------
1 | [{'title':'', 'description': ''}, ...]
2 | [{'title':'', 'description': ''}, ...]

Any ideas?

PS: The version of postgres is the latest 12, 13, 14 ...

Please provide enough code so others can better understand or reproduce the problem. — Community
– Community Bot, Commented Sep 15, 2021 at 12:13

ggordon · Accepted Answer · 2021-09-09 21:29:32Z

1

You may considering using json_to_record_set to extract your desired columns for each array element in posts before aggregating the results by the user id to obtain the desired elements.

Query #1

SELECT
    u.id,
    json_agg(
        json_build_object(
            'title',p.title,
            'description',p.description
        )
    ) as posts
FROM
    users u,
    json_to_recordset(u.posts) as p(
          title text,
          is_published text,
          description text,
          some_col text
    )
GROUP BY
    u.id
ORDER BY
    u.id;

id	posts
1	[{"title":"","description":""}]
2	[{"title":"","description":""}]

View on DB Fiddle

or shorter

Query #2

SELECT
    u.id,
    json_agg(p) as posts
FROM
    users u,
    json_to_recordset(u.posts) as p(
          title text,
          description text
    )
GROUP BY
    u.id
ORDER BY
    u.id;

id	posts
1	[{"title":"","description":""}]
2	[{"title":"","description":""}]

View on DB Fiddle

or

Query #3

SELECT
    u.id,
    (
        SELECT 
            json_agg(p.*) 
        FROM json_to_recordset(u.posts) as p(
             title text,
             description text
        )
      
    ) as posts
FROM
    users u;

View on DB Fiddle

Let me know if this works for you.

edited Sep 9, 2021 at 21:29

answered Sep 9, 2021 at 20:54

ggordon

10.1k2 gold badges18 silver badges29 bronze badges

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

3 Comments

Roman Zaycev Over a year ago

Thank you a lot! It is exactly what I'm searching for!

ggordon Over a year ago

@RomanZaycev Great! Please also mark this answer as the accepted answer so that this will help other Stackoverflow users to identify answers to a similar question they may have.

Roman Zaycev Over a year ago

Yah. Forgot to make it. Thank you once again!

Collectives™ on Stack Overflow

PostgreSql format jsonb array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related