How to return json formatted results for join query in postgres

Question

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.

Re (re)search success: Please before considering posting: Pin down code issues via minimal reproducible example. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at Meta Stack Overflow & Meta Stack Exchange. How much research effort is expected of Stack Overflow users? How to Ask Help center — philipxy
– philipxy, Commented Sep 7, 2022 at 3:25
How is googling, say, your title, as-is or per my comments, not giving you answers? How to transform a postgresql select with join to a json object? (Etc.) — philipxy
– philipxy, Commented Sep 7, 2022 at 9:44

Stefanov.sm · Accepted Answer · 2022-09-07 07:25:29Z

1

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;

edited Sep 7, 2022 at 7:25

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

answered Sep 7, 2022 at 3:35

lavantho0508

15513 bronze badges

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

Comments

Stefanov.sm · Accepted Answer · 2022-09-07 07:33:02Z

1

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;

answered Sep 7, 2022 at 7:33

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

1 Comment

Jackson Blue Over a year ago

I used this approach and it worked. Awesome, thank you

Collectives™ on Stack Overflow

How to return json formatted results for join query in postgres

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related