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