Converting results to array

Question

I have many entities like:

user

id | name
-----------
 1 | Joe
 2 | David
 3 | Jane

cars

id| name 
------------
1 | cars1 
2 | cars2 
3 | cars3 
4 | cars4 
5 | cars5 
6 | cars6 
7 | cars7 
8 | cars8 
9 | cars9 

cars_data

id | price  | category | uid | car_id
---+--------+----------+-----+-------
1  | 225.00 |   p1     |  1  |  1
2  | 451.00 |   p2     |  1  |  1
3  | 324.00 |   p2     |  1  |  2
4  | 784.00 |   p2     |  1  |  3
5  | 724.00 |   p3     |  1  |  2
6  | 214.00 |   p1     |  2  |  1
7  | 451.00 |   p1     |  2  |  2
8  | 926.00 |   p1     |  2  |  3
9  | 271.00 |   p2     |  2  |  3
10 | 421.00 |   p2     |  2  |  4
11 | 684.00 |   p2     |  2  |  2
12 | 124.00 |   p3     |  2  |  5
13 | 128.00 |   p3     |  2  |  1
14 | 741.00 |   p1     |  3  |  1
15 | 965.00 |   p1     |  3  |  3
16 | 124.00 |   p2     |  3  |  4
17 | 415.00 |   p2     |  3  |  1
18 | 51.00  |   p2    |  3  |   2
19 | 965.00 |   p2     |  3  |  6

filters

id  | name    |  filter          | uid
----+ --------+------------------+-----
1   | filter1 | string filters 1 |  1
2   | filter2 | string filters 2 |  1
3   | filter3 | string filters 3 |  1
4   | filter3 | string filters 3 |  1
5   | filter3 | string filters 3 |  1
6   | filter3 | string filters 3 |  1
7   | filter3 | string filters 3 |  1
8   | filter  | string filters 1 |  2
9   | filter5 | string filters 5 |  2
10  | filter6 | string filters 6 |  2
11  | filter6 | string filters 6 |  2
12  | filter6 | string filters 6 |  2
13  | filter6 | string filters 6 |  2
14  | filter7 | string filters 7 |  3
15  | filter8 | string filters 8 |  3
16  | filter8 | string filters 8 |  3
17  | filter8 | string filters 8 |  3
18  | filter8 | string filters 8 |  3
19  | filter9 | string filters 9 |  3

assign_filters

uid | category  | filter_id
----+ ----------+-----------
1   |   p1      |1          
1   |   p2      |1          
1   |   p2      |2          
1   |   p2      |3          
1   |   p3      |4          
2   |   p1      |9          
2   |   p1      |8          
2   |   p1      |13         
3   |   p2      |14         
3   |   p2      |16         
3   |   p2      |17         
3   |   p3      |19         
3   |   p3      |18         
3   |   p1      |14         
3   |   p1      |18         

What I want is results like this:

uid | category  | filter_id |car_id  
----+ ----------+-----------+--------
1   |   p1      |1          |[1]
1   |   p2      |1          |[1,2,3]
1   |   p2      |2          |[1,2,3]
1   |   p2      |3          |[1,2,3]
1   |   p3      |4          |[2]
2   |   p1      |9          |[1,2,3]
2   |   p1      |8          |[1,2,3]
2   |   p1      |13         |[1,2,3]
3   |   p2      |14         |[1,2,3,4,6]
3   |   p2      |16         |[1,2,3,4,6]
3   |   p2      |17         |[1,2,3,4,6]
3   |   p3      |19         |[6,7]
3   |   p3      |18         |[6,7]
3   |   p1      |14         |[1]
3   |   p1      |18         |[1]

How can I change the original SQL query to give me the result above?

dbfiddle

update:

• Each user can define a filter for himself and each filter is specific to one user.

• Each user can place cars in specific categories in the cars_data
  table.(categories is [p1,p2,p3])

• Each user can assign a number of filters to each of their categories.

I need to know which filters each user has used, and on which cars the filter has been applied.
For example, user number 1 has placed cars1, cars2, cars3 in category p1. Also, filter1, filter2, filter3 have been assigned to this category. The result I need is this:

uid | category  | filter_id |car_id  
----+ ----------+-----------+--------
1   |   p2      |1          |[1,2,3]
1   |   p2      |2          |[1,2,3]
1   |   p2      |3          |[1,2,3]

Answer 1

SELECT cd.uid, cd.category, f.id AS filter_id, f.name, f.filter, array_agg(cd.car_id) AS car_ids
FROM   cars_data cd
JOIN   assign_filters af USING (uid, category)
JOIN   filters f ON f.id = af.filter_id
GROUP  BY 1,2,3
ORDER  BY 1,2,3;

db<>fiddle here

I need to have the name and filter fields from the filters table (if the filter name values are not unique)

I use f.id AS filter_id instead of af.filter_id. As f.id is the PRIMARY KEY of the filters table, it covers all columns of the table in GROUP BY, and we don't need to add any additional columns from filters, that you want to include in the SELECT list.

About ordinal numbers in GROUP BY and ORDER BY

Ordinal numbers in the above query reference SELECT list items:

GROUP  BY 1,2,3
ORDER  BY 1,2,3

Equivalent to:

GROUP  BY cd.uid, cd.category, f.id
ORDER  BY cd.uid, cd.category, f.id

The verbose form is often preferred, quite emphatically by some. But it's not without caveats. The SQL standard has peculiar rules how to resolve identifiers in ORDER BY and GROUP BY - differently.

The manual:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

And:

If an ORDER BY expression is a simple name that matches both an output column name and an input column name, ORDER BY will interpret it as the output column name. This is the opposite of the choice that GROUP BY will make in the same situation. This inconsistency is made to be compatible with the SQL standard.

In this particular case we work with f.id instead of af.filter_id, but we want "filter_id" as alias (output column name) conflicting with the latter. Plus, there are three instances of uid. Two of those are merged in the USING clause. Plenty of ways to screw this up.

The safe way is to table-qualify everywhere as demonstrated. Expressions with conflicting aliases (none here) have to be spelled out repeatedly, no matter how verbose.

Ordinal numbers in ORDER BY and GROUP BY (and DISTINCT ON) are a simple, unambiguous alternative. Of course, changes to leading SELECT list items affect references, which may be welcome or not. It needs to be observed in any case, and that's the potential downside.

Answer 2

You can use the STRING_AGG function to accomplish this goal, so long as you first convert the car_id to a string:

STRING_AGG(car_id::character varying, ',') as car_id

This results in a SQL query that looks like:

SELECT cd.uid, cd.category, af.filter_id, STRING_AGG(cd.car_id::character varying, ',') as car_id
  FROM cars_data cd INNER JOIN assign_filters af ON af.uid = cd.uid and af.category = cd.category 
                    INNER JOIN filters f ON f.id = af.filter_id 
 GROUP BY cd.uid, cd.category, af.filter_id
 ORDER BY cd.uid;

Which gives you:

uid | category | filter_id | car_id
1   | p1       | 1         | 1
1   | p2       | 1         | 3,2,1
1   | p2       | 2         | 1,3,2
1   | p2       | 3         | 1,2,3
1   | p3       | 4         | 2
2   | p1       | 8         | 3,2,1
2   | p1       | 9         | 3,1,2
2   | p1       | 13        | 3,1,2
3   | p1       | 14        | 1,3
3   | p1       | 18        | 3,1

If you want the brackets, those can be concatenated:

CONCAT('[', STRING_AGG(car_id::character varying, ','), ']') as car_id