Get JSON array of rows with MySQL

Question

I want that MySQL will return a result set in a form of JSON array.

A worked query:

SELECT CONCAT(
    '[',
        GROUP_CONCAT(
            JSON_OBJECT(
                'ProductId', tblproducts.ProductId,
                'ProductName', tblproducts.ProductName
            )
        ),
    ']') As Products
FROM tblproducts;

This query returns the following JSON array:

[
    {
        "ProductId": "1",
        "ProductName": "Milk"
    },
    {
        "ProductId": "2",
        "ProductName": "Bread"
    }
]

Although this code works, I have a strong feeling that building a JSON array with GROUP_CONCAT and CONCAT is kind of workaround. If to use a JSON_ARRAY, a result set will consist of JSON arrays for each JSON_OBJECT.

Is there any native way to get a single JSON array with all JSON_OBJECT in the result set?

Mike · Accepted Answer · 2019-03-29 13:58:39Z

1

JSON_ARRAYAGG() could be what you're looking for.

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'ProductId', tblproducts.ProductId,
        'ProductName', tblproducts.ProductName
    )
) FROM tblproducts;

Here's an example: https://www.db-fiddle.com/f/uQ9UC7MDZM4gncNjViTsKw/0

edited Mar 29, 2019 at 13:58

Mike

14.8k32 gold badges122 silver badges182 bronze badges

answered Mar 2, 2019 at 17:44

ALFA

1,7441 gold badge12 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Get JSON array of rows with MySQL

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