How to return json formatted results for join query in postgres-CodePudding

I have this existing query:

SELECT to_json(table1) FROM table1

To return all of table in a nice json format. I need to add a join to this query, so my new query looks something like this:

SELECT (field1, field2, table2field1)
     FROM table1 INNER JOIN table2 ON field1 = table2id;

How can I get a similar style output in json format for this new query ? to_json no longer works.

Previous overflow questions like this do not show how to return all rows.

CodePudding user response：

you can try

WITH EXAMPLE (COL1,COL2,COL3) AS (
 SELECT field1, field2, table2field1
 FROM table1 INNER JOIN table2 ON field1 = table2id
)
SELECT TO_JSON(E) FROM EXAMPLE E;

CodePudding user response：

Or without a CTE. Use your query w/o brackets round the select list in the from clause

select to_json(t) from (... your select query here ...) t;

i.e.

select to_json(t) from
(
 SELECT field1, field2, table2field1
 FROM table1
 INNER JOIN table2 ON field1 = table2id
) t;