How to get column store object from 2 join tables in postgres-CodePudding

I have 3 table with excatcly same columns names. For this example lets say id,name,category, price.

when i extract data i got the desired data where each field renamed to source table. it is not so convinet and i want to extract data from same columns as arr of obj.

currently query:

SELECT 
     ta.name AS name_a,
     tb.name AS name_b,
     ta.price AS price_a,
     tb.price AS price_b,
     ta.category
FROM table_A ta
JOIN table_b tb on tb.id = ta.id

Currently result

id	name_a	name_b	price_a	price_b	category
1	name1	name2	x	y	cats
2	name3	name4	m	n	cats

Desired result

id	names	prices	category
1	{name_a:name1,name_b:name2}	{price_a:x,price_b:y}	cats
2	{name_a:name3,name_b:name4}	{price_a:m,price_b:n}	cats

CodePudding user response：

Here it is using jsonb_build_object function.

SELECT id,
     jsonb_build_object('name_a', ta.name, 'name_b', tb.name) AS names,
     jsonb_build_object('price_a', ta.price, 'price_b', tb.price) AS prices,
     ta.category
FROM table_A ta JOIN table_b tb using(id);

CodePudding user response：

SELECT ta.id AS id
   , array['name_a:' || ta.name, 'name_b:' || tb.name] AS names
   , array['price_a:' || ta.price, 'price_b:' || tb.price] AS prices
   , ta.category AS category
FROM table_A ta
JOIN table_b tb on tb.id = ta.id