Question 114781 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to return json formatted results for join query in postgres

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search