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

Postgresql – How to get column store object from 2 join tables in postgres

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
Tags:

2

Answers


  1. 0

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

Back To Top
Search