CODEWITHSUNDEEP
sqljsondatabasepostgresqljoin
Jackson Blue
Jackson Blue

Reputation: 11

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.

Upvotes: 1

Views: 440

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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;

Upvotes: 1

lavantho0508
lavantho0508

Reputation: 155

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;

Upvotes: 1

Related Questions