CODEWITHSUNDEEP
mysql
ryangus
ryangus

Reputation: 748

MYSQL array aggregate function like PostgreSQL array_agg

I got two tables on MYSQL, I wonder if there is any aggregate function on MYSQL as array_agg() FROM postgreSQL.

TABLE 1 properties Only have 8 records TABLE 2 records who captured the property, so sometimes can be 1 or n times for the same property, and I got this Qry:

SELECT p.id, pcb.users_admin_id as uid
FROM properties p
INNER JOIN prop_captured_by pcb ON p.id = pcb.property_id
-- GROUP BY p.id

id    uid
200   1
200   80
202   1
202   80
211   1
211   10
211   81
215   10 ...

If I use the GROUP BY part I get this:

id    uid
200   1
202   1
211   1
215   10 ...

Losing any other data than the first value for users_admin_id. I know that I can achieve my desired result with array_agg() function from postgreSQL but I can't figured out how to do it on MYSQL.

This is my desire Result:

id    uid
200   1,80 //an array, I don't mind about the separator, could be anything.
202   1,80
211   1,10,81
215   10 ...

I have tried UNION, GROUP BY, INNER JOIN... no luck... Any pointers?

UPDATE

I am using the many to many relations from this guy. Hopes it is useful for someone else. I needed to add the user's name from a third table, so the final query looks like this:

SELECT p.id, group_concat(pcb.users_admin_id) as uid, group_concat(ua.name) as uin
FROM properties p
INNER JOIN prop_captured_by pcb ON p.id = pcb.property_id
INNER JOIN users_admin ua ON ua.id = pcb.users_admin_id
group by p.id;

Upvotes: 40

Views: 53470

Answers (3)

Aryeh Leib Taurog
Aryeh Leib Taurog

Reputation: 5598

MySQL 5.7.22 introduced JSON_ARRAYAGG() and JSON_OBJECTAGG(). Since you want the user name as well, you could use the latter:

SELECT p.id, JSON_OBJECTAGG(pcb.users_admin_id, ua.name) as uin
FROM properties p
INNER JOIN prop_captured_by pcb ON p.id = pcb.property_id
INNER JOIN users_admin ua ON ua.id = pcb.users_admin_id
group by p.id;

DB Fiddle

Upvotes: 26

Rahul
Rahul

Reputation: 77896

You want to use GROUP_CONCAT() like

SELECT p.id, group_concat(pcb.users_admin_id) as uid
FROM properties p
INNER JOIN prop_captured_by pcb 
ON p.id = pcb.property_id
group by p.id;

Upvotes: 63

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I think you want group_concat():

SELECT p.id, GROUP_CONCAT(pcb.users_admin_id) as uids
FROM properties p INNER JOIN
     prop_captured_by pcb
     ON p.id = pcb.property_id
GROUP BY p.id;

This produces a comma-delimited string, but that is as close to an array that you get in MySQL.

Upvotes: 14

Related Questions