Converting one to many relation into a json column in PostgreSQL

Question

I'm trying to query two information_schema tables in PostgreSQL - tables and columns in order to get the following result:

table_name - columns_as_json_array

Sort of converting this one to many relation into a json array column. I tried a lot of different methods and came up with somethings like this:

SELECT t.table_name, c.json_columns
FROM information_schema.TABLES t
LEFT JOIN LATERAL(
  SELECT table_name, json_agg(row_to_json(tbc)) AS json_columns
  FROM information_schema.COLUMNS tbc
  WHERE t.table_name = tbc.table_name
  GROUP  BY table_name
  ) as c ON TRUE;

This results a list of table_names but the json_columns always contains all of the columns available instead of the columns of that certain table.

Any ideas?

GMB · Accepted Answer · 2020-04-04 17:01:33Z

3

I don't really see the point for a lateral join here. As far as concerns, you can get the expected results by aggregating information_schema.columns:

select table_name, json_agg(row_to_json(c)) json_columns
from information_schema.columns c
group by table_name
order by table_name

answered Apr 4, 2020 at 17:01

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Converting one to many relation into a json column in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related