How to build a JSON object in Postgres without explicitly referencing every single column

Question

I have the following query (Postgres 10.x):

select json_agg(
  json_build_object('id', m.id, 'name', m.name, 'folder_id', m.folder_id, 'created_at', m.created_at, 'updated_at', m.updated_at, 'item_type', m.item_type, 'deleted', m.deleted, 'deleted_at', m.deleted_at, 'virtual', m.virtual, 'duration', m.duration, 'filesize', m.filesize, 'original_filename', m.original_filename, 'snippet_detail', row_to_json(s.*))
)
from media m
join snippets as s on s.media_id = m.id
where item_type = 'SNIPPET' and source_media_id = '5e4ef00e-c6b7-4c24-8b4e-820acc5823d9';

It's obviously very tedious to have to list every single column in json_build_object. Is there some shorthand way to build a JSON object from m.*, while still keeping the last row_to_json result?

fog · Accepted Answer · 2021-05-24 21:35:26Z

1

You can use a subquery:

select row_to_json(x.*) from (
    select m.*, row_to_json(s.*) as snippet_detail
    from media m join snippets s on s.media_id = m.id
    where item_type = 'SNIPPET' and source_media_id = '5e4ef00e-c6b7-4c24-8b4e-820acc5823d9'
) x;

answered May 24, 2021 at 21:35

fog

3,4411 gold badge27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to build a JSON object in Postgres without explicitly referencing every single column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related