CODEWITHSUNDEEP
jsonpostgresql
Marcin Doliwa
Marcin Doliwa

Reputation: 3659

Generate json from query result

I have table locations:

country, city
USA, New York
USA, San Francisco
UK, London
UK, Bristol
Poland, Warsaw

Is there any way using postgres functions to generate json like this:

{
    'USA': ['New York', 'San Francisco'],
    'UK': ['London', 'Bristol'],
    'Poland': ['Warsaw']
}

Upvotes: 0

Views: 38

Answers (1)

S-Man
S-Man

Reputation: 23666

Click: demo:db<>fiddle

SELECT 
   json_object_agg(country, cities)    -- 2
FROM (
   SELECT
      country,
      json_agg(city) as cities         -- 1
   FROM
       locations
   GROUP BY country                    -- 1
) s
  1. Aggregate the city elements by their country into one JSON array using json_agg()
  2. After that you are able to aggregate all rows into one JSON object using json_object_agg()

Upvotes: 1

Related Questions