CODEWITHSUNDEEP
mysqlarraysjson
Mike
Mike

Reputation: 14586

Get JSON array of rows with MySQL

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?

Upvotes: 0

Views: 111

Answers (1)

ALFA
ALFA

Reputation: 1744

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

Upvotes: 1

Related Questions