jsonb/array reformatting query

December 4, 2024

If I have a table which contains the following:

id: 1,
names: ["Dave", "Pete", "Eric"],
ages: [30, 40, 50]

(types are id: int, names: char varying[], ages: int[])

how can I query that to return this:

id: 1,
people: [{"name": "Dave", "Age": 30}, {"name": "Pete", "Age": 40}, {"name": "Eric", "Age": 50}]

(types: id: int, people: jsonb (or jsonb[]))

Thanks

>Solution :

You need to use jsonb_build_object to firstly create json objects with name and id data for each record and then combine all objects and form json array using jsonb_agg then group the result based on id to get desired result

Some thing like this :

 SELECT
  id,
  jsonb_agg(
    jsonb_build_object(
      'name', names[i],
      'Age', ages[i]
    )
  ) AS people
 FROM my_table_name, generate_series(1, array_length(names, 1)) AS s(i) GROUP BY id;