How to get values ​from an array in Oracle PL/SQL

Question

I have the following problem.

I'm receiving data from an external API and I need to store the tag data that is coming as an array.

I already tried to do it the following way, but without success.

DECLARE
 v_name         VARCHAR2(100);
 v_email        VARCHAR2(100);

 TYPE t_tags IS TABLE OF VARCHAR2(100);
 v_tags t_tags;
BEGIN

 SELECT name, email
 INTO v_name, v_email
 FROM JSON_TABLE(:body, '$'
    COLUMNS (
        name  VARCHAR2(100)  PATH '$.leads.name',
        email VARCHAR2(100)  PATH '$.leads.email'
    );

 SELECT CAST(COLLECT(tag) AS t_tags)
    INTO v_tags
    FROM JSON_TABLE(:body, '$.leads.tags[*]'
        COLUMNS (tag VARCHAR2(100) PATH '$'));

 IF v_tags IS NOT NULL OR v_tags.COUNT > 0 THEN
  FOR i IN 1 .. v_tags.COUNT LOOP
     v_tag := v_tags(i);
    
    INSERT INTO LEAD_TAG (TAG, COR, ETQ_CAT)
            VALUES ('TESTING', 'TESTING', 'TESTING')
            RETURNING ID INTO v_tag_id;
  END LOOP;
 END IF;
END;

could anyone help me?

Answer 1

You do not need a collection. Use INSERT ... SELECT from the JSON_TABLE.

BEGIN
  INSERT INTO LEAD_TAG (TAG, COR, ETQ_CAT)
  SELECT tag, name, email
  FROM   JSON_TABLE(
           :body,
           '$.leads'
           COLUMNS (
             name  VARCHAR2(100)  PATH '$.name',
             email VARCHAR2(100)  PATH '$.email',
             NESTED PATH '$.tags[*]' COLUMNS (
               tag VARCHAR2(100) PATH '$'
             )
           )
         );
END;
/

Which, for the table:

CREATE TABLE lead_tag(
  tag VARCHAR2(20),
  cor VARCHAR2(20),
  etq_cat VARCHAR2(20)
);

Then is :body contains:

{"leads":{"name":"Alice","email": "[email protected]","tags":["A","B"]}}

Then, after the INSERT, the table contains:

TAG	COR	ETQ_CAT
A	Alice	[email protected]
B	Alice	[email protected]

fiddle