CODEWITHSUNDEEP
sqljsonpostgresqlaggregate-functions
Tom
Tom

Reputation: 1468

Postgres SQL Query that returns JSON object with keys from one column and values from another column

I want to create a (Postgres) SQL Query that returns a JSON object. The keys should be the entries from one column (eg. ID) and the values the entries from another column (eg. name). My table looks like this:

CREATE TABLE foods (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
INSERT INTO foods(name)
VALUES  ('Apple'),
        ('Banana'),
        ('Lemon');

Running example: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=96c8ee3de02647333752a30b9cfc8674

The result should look like this:

{
  "1": "Apple",
  "2": "Banana",
  "3": "Lemon"
}

Upvotes: 2

Views: 956

Answers (1)

GMB
GMB

Reputation: 222682

Just use json[b]_object_agg():

select jsonb_object_agg(id, name) res
from foods

Upvotes: 3

Related Questions