In Informix 12.10, processing a JSON array like the one you have in a stored procedure can indeed be done iteratively using a loop, but there is a more elegant way to handle this, especially for parsing and processing JSON arrays in a database context. Informix provides BSON and JSON functions to work with such data.
Here is a solution:
Steps to Parse and Process JSON Arrays in Informix
Insert the JSON into a BSON/JSON column or temporary variable
First, store the JSON data into a BSON/JSON variable or a table’s JSON/BSON column so it can be processed.
Use a Loop to Extract Each Array Element
You can use the bson_count function to determine the number of elements in the DATA array and then iterate over it.
Extract Values Dynamically
Use bson_get or bson_value functions to retrieve specific values dynamically inside the loop.
Example Stored Procedure
CREATE PROCEDURE process_json(json_param LVARCHAR(30000))
DEFINE jso BSON;
DEFINE data_array BSON;
DEFINE num_elements INTEGER;
DEFINE idx INTEGER;
DEFINE route_value LVARCHAR(100);
DEFINE route_status LVARCHAR(10);
-- Convert input LVARCHAR to BSON
LET jso = bson(json_param);
-- Extract the 'DATA' array
LET data_array = bson_get(jso, "DATA");
-- Get the number of elements in the array
LET num_elements = bson_count(data_array);
-- Iterate through each element in the array
FOR idx = 0 TO num_elements - 1
-- Extract the 'route' and 'value' fields
LET route_value = bson_value(data_array, idx || ".route");
LET route_status = bson_value(data_array, idx || ".value");
-- Perform desired database manipulations (example)
UPDATE my_table
SET status = route_status
WHERE route = route_value;
END FOR;
END PROCEDURE;
Explanation of the Code
BSON Conversion: The input JSON (LVARCHAR) is converted to a BSON object using bson().
Array Extraction: The DATA array is extracted from the BSON object using bson_get.
Element Count: The number of elements in the array is determined using bson_count.
Dynamic Access: Loop through the array, using bson_value with dynamic keys (idx || ".field_name"), to extract the fields for each element.
Processing: Update or manipulate the database based on the extracted values.
Key Functions
bson(): Converts JSON string to BSON.
bson_get(bson_obj, "path"): Retrieves a sub-object or array.
bson_count(array): Returns the number of elements in a BSON array.
bson_value(bson_obj, "key"): Extracts a value from a BSON object.
If you prefer not to count elements upfront, you could iterate by continuously fetching elements until a NULL or error occurs. However, counting elements first (as shown above) is more robust and avoids unnecessary errors.