Search code examples
jsondatabasepostgresql

How to return JSON objects without explicit aliasing in postgresql

If I have two tables and I join them, can I return one of the values a JSON object, without having to specify all the fields?

For example, the following works:

SELECT u.*, 
json_build_object(
 'name', p.name, 
 'content', p."content") AS post
FROM user AS u
INNER JOIN post as p ON p.id = o."postId"

My question now is if I can use json_build_object (or something to that effect) simply by passing p.*, without having to manually specify all columns.

Does anyone know how this could work?

Solution

  • Using to_json as @AdrianKlaver comments will do exactly what you need.

    SELECT u.*, to_json(p) AS post
FROM user AS u
INNER JOIN post as p ON p.id = o."postId";