Batch Insert in Stored Procedure

Help Procedural SQL
1

I am trying to optimize the stored proc by using the Batch processing approach. The stored proc gets invoked by the kafka pipeline.

Pipeline:

CREATE or REPLACE PIPELINE test_proc_pipeline AS LOAD DATA KAFKA ‘<broker_host>:/test_topic’ BATCH_INTERVAL 25 INTO PROCEDURE test_proc;

Procedure:-

DELIMITER //
CREATE OR REPLACE PROCEDURE test_proc(batch query(myjson text)) AS
BEGIN

INSERT INTO test_orders(id, amount) SELECT myjson.body::$id, myjson.body::$amount FROM batch;

END //
DELIMITER ;

This is not working for the kafka message below with the error “myjson.body” is an unknown variable.

{
“traceId”: “123a”,
“body”: {
“id”: “34123”,
“amount”: “10.5”
}
}

2

Hello, it should be myjson::body instead of myjson.body (assuming body is the name of a key in the json)

3

It works for this simple JSON. How can we get the data from an array?
In the example below, We have dataList array.
How can I get the value from the first element of the array for the batch insert?

In a stored proc, Where we process record by record from the batch, we get the value from the array as below.
json_to_array(JSON_EXTRACT_JSON(myJson::body, ‘dataList’))[0];

{
“traceId”: “123a”,
“body”: {
“id”: “34123”,
“amount”: “10.5”,
“dataList”: [
{
“name” : “First”,
“loc” : “123”
},
{
“name” : “Second”,
“loc” : “456”
}
]
}
}

4

You can try:
myJson::body::dataList::0

if you want to flatten the array then you do something like/;
table(json_to_array(myJson::body::dataList))

5

You can also use json_extract_… to get values from an array:

or use this kind of notation:
select json_to_array(“[1,2]”)[1];

6

myJson::body::dataList::0::$name is throwing compilation error.

7

Can we use select json_to_array(“[1,2]”)[1]; for the batch processing within the stored proc?

INSERT INTO test_orders(id, amount, name) SELECT myjson::body::$id, myjson::body::$amount, json_to_array(myjson::body::$dataList)[0].name FROM batch;

This function is not working if it is being used in the select clause.
Is this correct?

Will there be a performance hit if i need to read 100 fields from the array like

SELECT myjson::body::$id, myjson::body::$amount, json_to_array(myjson::body::$dataList)[0].name,
json_to_array(myjson::body::$dataList)[0].name1, json_to_array(myjson::body::$dataList)[0].name2, …, json_to_array(myjson::body::$dataList)[0].name100, FROM batch

8

sorry backticks got stripped somehow

meant to write

myJson::body::dataList::`0`