0

I have two tables , on parent table (categories ) and child table (features).. I have this query

    select c.* , 
            (select GROUP_CONCAT(f.featuresId , f.featuresTitle ) 
            from applika.features f  
            where f.categoriesId = c.categoriesId 
            group by categoriesId) as nestedData 
        from applika.categories c  

PROBLEM : but in nestedData column my subquery in group_concat show the result inside quotes like this :

 "nestedData": "featuresId:1featuresTitle:title1 ,featuresId:2featuresTitle:title2 ,featuresId:3featuresTitle:title3 ,featuresId:4featuresTitle:4"

REQUEST : but I don't want to show data in quotes , is there another way instead of GROUP_CONCAT or CONCAT to select my child data in ordinary array of objects like this for example :

    [
        ...

 "nestedData": [
          {"featuresId":1 , "featuresTitle":"title1"},
          {"featuresId":2 , "featuresTitle":"title2"} ,
          {"featuresId":3 , "featuresTitle":"title3"},
          {"featuresId":4 , "featuresTitle":title4"}
        ]
    ]
2
  • Do you want the result to be JSON? Take a look at this: dba.stackexchange.com/questions/192208/…
    – tttony
    Jul 4, 2019 at 21:03
  • MySQL never adds quotes until you ask for it directly (for example, by using JSON function). So I see three variants. 1) Quotes already exists in source data. 2) Quotes are added by your client software. 3) applika.features is a view which uses some JSON function. Variant 2 is preferred.
    – Akina
    Jul 5, 2019 at 5:03

1 Answer 1

0
SELECT
    c.*,
    JSON_ARRAYAGG(JSON_OBJECT('featuresId', f.featuresId, 'featuresTitle', f.featuresTitle)) AS nestedData
FROM
    applika.categories c
LEFT JOIN
    applika.features f
    ON f.categoriesId = c.categoriesId
GROUP BY
    c.categoriesId;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.