CODEWITHSUNDEEP
sqlarraysjsonpostgresqlaggregate
brtk
brtk

Reputation: 117

Aggregate rows into one by id - postgres

Example:

user_id activity_type interested_rooms
1       views         1,2
1       application   1
1       search        3,2

I would like to get result: 

user_id interested_rooms  
1       "1","2","3"

Then I would like to search all users who for example where interested in 1 room

SELECT user_id
FROM blabla
WHERE interested_rooms IN "1"

How can I get this result by using SQL (postgreSQL)?

Upvotes: 1

Views: 375

Answers (1)

Jim Jones
Jim Jones

Reputation: 19623

There are many ways to achieve what you want. Considering that your table has the following structure ..

CREATE TABLE blabla (user_id INT,activity_type VARCHAR,interested_rooms VARCHAR);
INSERT INTO blabla VALUES (1,'views','1,2'),
                          (1,'application','1'),
                          (1,'search','3,2');

.. you could try a CTE ..

WITH j AS (
  SELECT DISTINCT user_id, unnest(string_to_array(interested_rooms,',')) AS rooms
  FROM blabla ORDER BY rooms
)
SELECT user_id, array_agg(rooms) AS interested_rooms
FROM j GROUP BY user_id

.. that would return the result you want

 user_id | interested_rooms 
---------+------------------
       1 | {1,2,3}

Note that the usage of the functions unnest and string_to_array will depend on the data type of the column interested_rooms which wasn't provided in your question. But it should give you an orientation of what to do. Good luck!

Upvotes: 1

Related Questions