--# 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'
))
@

| 元素_编号|
| - -|
| 2个|