DB2 length of Clob (JSON)

Question

I am generating a json inside a stored procedure as like

Declare res CLOB(5M);
Set res =  (values (json_array(select json_object…

Json Looks like

[{pk: 1, name1: xyz, name: 2}, {pk: 2, name1: cvc, name2: vcc}]

At the end I Need the Information what is the length of the json, means How many entries do it have, beginning from Root.

I need something like this,

Declare counter SMALLINT;
Set Counter = xyz —should be 2

So How can I find out from res, that there are two rows?

Answer 1

--# SET TERMINATOR @

-- Create a generic function to deal with JSON arrays
CREATE OR REPLACE FUNCTION UNNEST_JSON (P_DOC CLOB(1M), P_PATH VARCHAR(128))
RETURNS TABLE
(
  INDEX INT
, ITEM  CLOB (1M)
)
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN 
  DECLARE L_IDX INT DEFAULT 0;
  
  L1: 
  WHILE TRUE DO
    IF NOT JSON_EXISTS (P_DOC, P_PATH || '[' || L_IDX || ']') THEN LEAVE L1; END IF; 
    PIPE (L_IDX, JSON_QUERY (P_DOC, P_PATH || '[' || L_IDX || ']'));
    SET L_IDX = L_IDX + 1;
  END WHILE L1;
  
  RETURN;  
END
@

SELECT COUNT (1) AS ELEM_NUM
FROM TABLE (UNNEST_JSON (
  -- You give your unnamed array some name you will refer in the 2-nd arg
  JSON_OBJECT
  (
    KEY 'items'
    VALUE    
    JSON_ARRAY
    (
      JSON_OBJECT (KEY 'pk' VALUE 1, KEY 'name1' VALUE 'xyz', KEY 'name'  VALUE 2) FORMAT JSON
    , JSON_OBJECT (KEY 'pk' VALUE 2, KEY 'name1' VALUE 'cvc', KEY 'name2' VALUE 'vcc') FORMAT JSON
    )
    FORMAT JSON
  )  
  , '$.items'
))
@

ELEM_NUM
2