Convert mysql table data to JSON nested format-CodePudding

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...

CodePudding user response：

If you are getting this table in UI then you can use json.Stringify(data) .

In case you are fetching this data at server end then you must share your server side code. Each and every language has its own way to convert data into json.

CodePudding user response：

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;