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