[SOLVED] Convert json array to character list esql

Convert json array to character list esql

I have the following json syntax

{
"PRINTER": "P123", 
"ID_INPUT": "111046", 
"IDCOUNT": 3, 
"TIME": "",
"IDLIST": [{ID_IN": "111046"},{"ID_IN": "111047"}, {"ID_IN": "111048"}]
}

My goal is to iterate over IDLIST and create a character string like "111046;111047;111048" in esql. But I have problems on processing the json. I tried:

DECLARE c       INTEGER;   -- cardinality
DECLARE i       INTEGER 0; -- index
DECLARE idlist  CHARACTER '';

-- find cardinality
SET c = CARDINALITY(InputRoot.JSON.Data.IDLIST[]); -- Q1
            
-- iterate over IDLIST
WHILE i < c DO
  -- build new data structure
  SET idlist = idlist || InputRoot.JSON.Data.IDLIST[i] || ';'; -- Q2
  SET i = i + 1;
END WHILE;

Q1: Is it possible to get the cardinality this way?
Q2: Can I write my data structure like this?
Q3: Is there a better way to do this?

Solution

Using cardinality function is not necessary to get the desired string value. Also, cardinality is an expensive(processor intensive) function in IIB esql.

    DECLARE idlist CHAR '';
    FOR PTR AS InputRoot.JSON.Data.IDLIST.Item[] DO
        SET idlist = idlist || PTR.ID_IN  || ';'    ;           
    END FOR;
    
    SET idlist = TRIM(TRAILING ';' FROM idlist); --This is to get rid of the last ; in the string