Parse JSON array to table in SP

General
1

Informix 12.10

I’m receiving in stored procedure LVARCHAR(30000) parameter JSON data like:

EXECUTE PROCEDURE "dbtest".fw_route_change("{DATA:[{route:""123"",value:""1""},{route:""66778"",value:""1""},{route:""8765"",value:""0""}]}");

The target is to parse received array and do some manipulations in DB based on its values.

With this command i can read the route value in 3rd element (example!)

SELECT BSON_VALUE_int(bson_get(jso, "DATA.2.route"), "route") FROM _tmp_spl;

F.e. received 8765 and it’s OK.

But how i can read all array elements for future processing like:

foreach 
     select BSON_MAGIC_FUNC_RET_ROUTE_VALUE into _my_var from _tmp_spl

     --doSmth(_myvar)
 end foreach;

Or i need really go 1 by one on this JSON array until received nothing from BSON_VALUE_int(bson_get(jso, "DATA.2.route"), "route")?

Thank You!

2

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

  1. 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.
  2. 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.
  3. 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

  1. BSON Conversion: The input JSON (LVARCHAR) is converted to a BSON object using bson().
  2. Array Extraction: The DATA array is extracted from the BSON object using bson_get.
  3. Element Count: The number of elements in the array is determined using bson_count.
  4. Dynamic Access: Loop through the array, using bson_value with dynamic keys (idx || ".field_name"), to extract the fields for each element.
  5. 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.

Example Input and Execution

Input:

EXECUTE PROCEDURE process_json(
    '{ "DATA": [ { "route": "123", "value": "1" }, { "route": "66778", "value": "1" }, { "route": "8765", "value": "0" } ] }'
);

Example Table my_table:

route status
123 NULL
66778 NULL
8765 NULL

Output After Execution:

route status
123 1
66778 1
8765 0

Alternative: Iterating Until Nothing is Found

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.