Parse a nested json in memsql

Help Procedural SQL
1

Hello
Is there a way to parse nested json in memsql. for inserting into a flat table
Here is the example

{
“serviceDate”: 1585759961,
“recapGroupId”: “13-rgetd-hgrtso”,
“vendorNb”: 54231,
“itemsId”: “fgrhr-hdgdt”,
“activities”: [
{
“activityId”: “1111-hdgdt”,
“companyId”: 1111,
“transactions”: [
{
“transactionId”: 123,
“cost”: 100
},
{
“transactionId”: 459,
“cost”: 200
}
]
},
{
“activityId”: “222222-hdgdt”,
“companyId”: 2222,
“transactions”: [
{
“transactionId”: 256,
“cost”: 300
},
{
“transactionId”: 456,
“cost”: 400
}
]
}
]
}

2

Your best bet right now would be to write a stored procedure (SP) to extract the desired information and insert it into the target table. The JSON_TO_ARRAY() function shipped in 7.0 could make that SP easier to write. Or, flatten it from a client-side application and insert it to the desired table.

What do you want the result to look like exactly (in the target table) when you’re done?

Keep your eyes open for features in 7.1, planned for late spring, that will make this easier still.

3

Using SP but would like to convert transactions from example into tuples and do a bulk inset into table.Any workable solution for this in v6.8.

4

with 7.1 can we do bulk inserts without looping. from above example i would like services and activities to go in one table and transactions in another table.
we are using SP but have to loop through.

Also lets say I want to check if Json array activities contains any activity whose companyId is 222 and if present take some certain actions , how can it be achieved with 7.1 functions.

Do we have functions similar to json_contains of MYSQL ? or ```
json_extract(data, “$[*].companyId”)=2222 when we want to extract record with companyID as 2222 but do not know the index in array of this record.