Convert mysql table data to JSON nested format

Issue

I am getting data from MySQL table in the below format :

sName  sId  pId   sNumber   qty   price
test    3    1     12345     10    10
test    3    1     12345    750    9
test    3    1     12345    1500   8
test    3    1     12345    3000   7
test    3    1     34567     10    20
test    3    1     34567    750    15
test    3    1     34567    1500   14
test    3    1     34567    3000   12

I need above data converted into JSON like below:

[{
    "sName": "test",
    "sId": 3,
    "pId": 1,
    "sNumber": 12345,
    "Lists": [{
        "qty": 10,
        "price": "10"
    }, {
        "qty": 750,
        "price": "9"
    }, {
        "qty": 1500,
        "price": "8"
    }, {
        "qty": 3000,
        "price": "7"
    }]
}, {
    "sName": "test",
    "sId": 3,
    "pId": 1,
    "sNumber": 34567,
    "Lists": [{
        "qty": 10,
        "price": "20"
    }, {
        "qty": 750,
        "price": "15"
    }, {
        "qty": 1500,
        "price": "14"
    }, {
        "qty": 3000,
        "price": "12"
    }]
}]

Can you please help me what is the better way to achieve this ? Also need to consider performance because data might grow.

Thanks…

Solution

As long as you are using a relatively recent version of MySQL (5.7.8 or greater) the simplest approach might be in the structuring of your query. This can be accomplished with GROUP_CONCAT and JSON_OBJECT.

SELECT 
sName, sId, pId, sNumber,
GROUP_CONCAT(
  JSON_OBJECT(
    'qty', qty,
    'price', price
  )
) AS Lists
FROM your_table_name_here
GROUP BY sName, sId, pId, sNumber;

Answered By – dusthaines

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0