postgresqlpostgresql-json

How to select all records from a 2-columns table as a single JSON object?


I have prepared a DB Fiddle for my question.

In PostgreSQL 15 I have a table of English words.

Some of them have an explanation/description and some not:

create table words (word text not null, explanation text);

insert into words (word, explanation) values
    ( 'AAHED' , null ),
    ( 'AAH' , 'To exclaim in delight' ),
    ( 'AAL' , 'East Indian shrub' ),
    ( 'AARDVARK' , null );

I would like to select them as the following JSON object (and dump to a JSON file at my web server, for later inclusion in a Javascript app):

{
  "AAHED" : "",
  "AAH" : "To exclaim in delight",
  "AAL" : 'East Indian shrub",
  "AARDVARK" : ""
}

So I have tried the following queries, but cannot get the desired result yet:

select json_build_object(word, explanation) from words;

with cte as (select word, explanation from words) select row_to_json(c) from cte c;

What would be the suitable JSON function here please?


Solution

  • It's json_object_agg(), because you need an aggregation of records:

    demo:db<>fiddle

    SELECT
        json_object_agg(word, explanation) 
    FROM words;