Json Object to json Array

Hi All, I’m wondering if there’s a way in singlestore to convert a json object to json array (my objective is to parse it using the TABLE() function).

The json I have to parse:
“data”: {
“data_1236_buy”: 2,
“data_1534_buy”: 3,
“data_1434_buy”: 1,
“data_1233_sell”: 2,
“data_1234_buy”: 3,
“data_1236_sell”: 2
}

the expected result:

[
{
“id”: “data_1236_buy”,
“value”: 2
},
{
“id”: “data_1534_buy”,
“value”: 3
},
{
“id”: “data_1434_buy”,
“value”: 1
},
{
“id”: “data_1233_sell”,
“value”: 2
},
{
“id”: “data_1234_buy”,
“value”: 3
},
{
“id”: “data_1236_sell”,
“value”: 2
}
]

At the end what I need is to sum the values of all the fields ending on *_sell. Maybe there’s another option to accomplish this without creating an array

1 Like

If there’s a small number of fields of the JSON object you want to sum, and you know their names in advance, you could just extract each field and add them together in an expression in a SQL statement. Will that work for you?