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?
It's json_object_agg()
, because you need an aggregation of records:
SELECT
json_object_agg(word, explanation)
FROM words;