PostgreSql format jsonb array

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 …

Answer

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.