JSON_ARRAYAGG not working properly with WHERE clause

Question

I have two tables Contractor Users as cu & Parkings as pak. But when we search the parking name our contractor parking also get affected by this search

Contractor Users as cu

id	name
1	Tom
2	Jerry
3	Jake

Parkings as pak

id	name	contractor_ids
1	parking_a	1
2	parking_b	1,3
3	parking_c	1,2

Query before any search

 SELECT cu.name, JSON_ARRAYAGG(pak.name) as user_parkings 
FROM contractor_users as cu 
LEFT JOIN parkings as pak ON FIND_IN_SET(cu.id, pak.contractor_ids) 
WHERE cu.name IS NOT NULL 
GROUP BY cu.id

Result of Above query

name	user_parkings
Tom	['parking_a','parking_b', 'parking_c']
Jerry	['parking_c']
Jake	['parking_b',]

Add search parking name filter in WHERE clause

SELECT cu.name, JSON_ARRAYAGG(pak.name) as user_parkings 
FROM contractor_users as cu 
LEFT JOIN parkings as pak ON FIND_IN_SET(cu.id, pak.contractor_ids) 
WHERE cu.name IS NOT NULL AND pak.name = 'parking_a' 
GROUP BY cu.id

Result of Above query

name	user_parkings
Tom	['parking_a']

So here is the problem you see user_parkings column also filtered but what I want after adding the search

name	user_parkings
Tom	['parking_a','parking_b', 'parking_c']

Storing the data in CSV format is bad idea. Normalize your structure. — Akina
– Akina, Commented Nov 18, 2022 at 8:58

Akina · Accepted Answer · 2022-11-18 08:56:16Z

1

Add search parking name filter in WHERE clause

This is incorrect - you need to check the value presence after the aggregation, so the condition must be placed not into WHERE but into HAVING.

Test, for example, this:

SELECT cu.name, JSON_ARRAYAGG(pak.name) as user_parkings 
FROM contractor_users as cu 
LEFT JOIN parkings as pak ON FIND_IN_SET(cu.id, pak.contractor_ids) 
WHERE cu.name IS NOT NULL  
GROUP BY cu.id
HAVING MAX(pak.name = 'parking_a')

answered Nov 18, 2022 at 8:56

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON_ARRAYAGG not working properly with WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related