Returning array of json objects from SQL

Question

I'm new to SQL so this may be a dumb question but I'm having trouble figuring out returning an array of JSON objects.

My code is

SELECT JSON_OBJECT(
            'title', pc.title,
            'reviews', (SELECT CAST(CONCAT('[',
                GROUP_CONCAT(
                    JSON_OBJECT(
                        'username',r.uname,
                        'review',r.review,
                        'date', r.date_added
                    )
                )
            , ']')
            AS JSON) FROM reviews r WHERE pc.pid=r.pid)
        ) AS JSON
        FROM product_comp AS pc;

When there are no reviews it properly returns an empty array and when there is one review it returns that review in an array. But if there are multiple reviews it returns an empty array. What am I missing?

Possible duplicate of How do I generate nested json objects using mysql native json functions? — EternalHour
– EternalHour, Commented Apr 23, 2019 at 17:22
Well @JoakimDanielson that would be the safe method using JSON_ARRAY function.. But if you know how JSON structure works you can generate JSON with CONCAT and or GROUP_CONCAT functions also, it does not really matter. — Raymond Nijland
– Raymond Nijland, Commented Apr 23, 2019 at 17:28

Bill Karwin · Accepted Answer · 2019-04-23 17:32:29Z

1

Instead of doing the error-prone work of formatting JSON using CONCAT, try using JSON_ARRAYAGG().

SELECT JSON_OBJECT(
  'title', pc.title,
  'reviews', JSON_ARRAYAGG(
               JSON_OBJECT(
                 'username', r.uname,
                 'review', r.review,
                 'date', r.date_added
               )
             )
)
FROM product_comp AS pc
LEFT OUTER JOIN reviews AS r ON pc.pid = r.pid
GROUP BY pc.pid

I have not tested this, but it should give you the right idea.

answered Apr 23, 2019 at 17:32

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Collectives™ on Stack Overflow

Returning array of json objects from SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related