Convert mysql table data to JSON nested format

Question

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

Your sample data doesn't make any sense because you've got lists in each value with the same overlapping data. — Altimus Prime
– Altimus Prime, Commented Nov 3, 2021 at 14:07

dusthaines · Accepted Answer · 2021-11-03 17:24:46Z

1

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 Nov 3, 2021 at 17:24

dusthaines

1,3501 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajan Mishra · Accepted Answer · 2021-11-03 14:07:04Z

0

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.

answered Nov 3, 2021 at 14:07

Rajan Mishra

1,1782 gold badges15 silver badges31 bronze badges

3 Comments

user2427573 Over a year ago

Converting JSON directly I can but I need the format means same sNumber can have array of qty/price. If I convert directly then each row will be separated in JSON. Its basically group.

Rajan Mishra Over a year ago

in which tech you are doing this

user2427573 Over a year ago

Node typescript

Collectives™ on Stack Overflow

Convert mysql table data to JSON nested format

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related