php - I have problem on one to many relation querying in SQL with JSON functions

Question

I have a where condition on inner table and when I try to fetch results with JSON functions it does not find any record with inner join but it works with without where condition. Any suggestions here is my query. I am using mysql 8 version.

Here is my code:

SELECT 
    JSON_PRETTY(JSON_ARRAYAGG(JSON_OBJECT( 'id', D.id, 'name', D.name, 'labels', JLabel.labels))) AS `data` 
FROM 
    deals AS D 
INNER JOIN 
    (SELECT 
         DL.id,
         DL.label_id AS label_id,
         DL.deal_id AS property_deal_id,
         JSON_ARRAYAGG(JSON_OBJECT('deal_id', DL.deal_id, 'label_id', DL.label_id)) AS `labels` 
     FROM 
         deal_label AS DL
     GROUP BY 
         DL.deal_id) AS `JLabel` ON D.id = JLabel.property_deal_id  
WHERE 
    JLabel.label_id = 2

It show results with 1 id but not find any record with ...

Answer 1

等待大神答复