I have the following schema here: http://sqlfiddle.com/#!17/5c73a/1
I want to create a query where the results will be something like this:
id | tags
_________________________________
1. | [{"id": "id", "title": "first"}, {"id": "id", "title": "second"},{"id": "id", "title": "third"}]
2 | [{"id": "id", "title": "fourth"}, {"id": "id", "title": "fifth"},{"id": "id", "title": "sixth"}]
The idea is to build an array with an object for each line of the array, the important is the title variable
CodePudding user response：
You need to unnest the array and then aggregate it back:
select t.id, jsonb_agg(jsonb_build_object('id', 'id', 'title', tg.title))
from things t
cross join jsonb_array_elements(tags) as tg(title)
group by t.id;