Json Object to json Array

Help Procedural SQL
1

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
2

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?

3

thanks for your reply, I resolved it by manipulating strings into a store procedure. It’s a very unusual case, actually