can anyone help me ? Im not very good with sql....

I'm trying to put the output of this example in a table

I have this scenario:

DECLARE
  v_project_id   NUMBER(15);
  v_group_name   VARCHAR2(150);
  v_user_id      NUMBER(15);
  v_group_id     NUMBER(15);
BEGIN
   FOR c IN (
             SELECT project_id
               FROM JSON_TABLE(:projectList,
                                    '$' COLUMNS(NESTED PATH '$.projects[*]'
                                                COLUMNS(
                                                        project_id  VARCHAR2(150) PATH '$.project.id'
                                                        )
                                                )
                               )
            )
   LOOP
     v_project_id  := c.project_id;
     v_group_name:= json_value (:projectList,'$.group_name');
     v_user_id:= json_value (:projectList,'$.user_id');
     v_group_id:= json_value (:projectList,'$.group_id');
     DBMS_OUTPUT.PUT_LINE(v_project_id||' - '||v_group_name||' - '||v_user_id||' - '||v_group_id);
   END LOOP;
END;

When I run this procedure, appear a box to fill with the bind variables and I filled with this json parameters:

{
    "group_name" : "Grupo 1",
    "user_id" : 12345,
    "group_id": 10001,
    "projects": 
    [
        {
            "project" : {
                "id" : "721"
            }
        },
        {
            "project" : {
                "id" : "722"
            }
        },
        {
            "project" : {
                "id" : "723"
            }
        },
        {
            "project" : {
                "id" : "724"
            }
        }
    ]
}

and after ok, this is the result:

721 - Grupo 1 - 12345 - 10001
722 - Grupo 1 - 12345 - 10001
723 - Grupo 1 - 12345 - 10001
724 - Grupo 1 - 12345 - 10001

Now is the question:

how can I insert this output in a table called APP_PROJECT_GROUP with this structure ??

TABLE APP_PROJECT_GROUP ( 
  group_id      NUMBER   (15)    NOT NULL, 
  group_name    VARCHAR2 (150)   NOT NULL, 
  project_id    NUMBER   (15)    NOT NULL, 
  user_id       NUMBER   (15)    NOT NULL, 
  PRIMARY KEY ( group_id ) 
 )

Im trying many ways, but I cant solve this

CodePudding user response：

You cannot because you have an identical group_id for all the projects in the JSON but you also have a PRIMARY KEY on the group_id so they must all be unique. If you do not include the PRIMARY KEY in your table definition:

CREATE TABLE APP_PROJECT_GROUP ( 
  group_id      NUMBER   (15)    NOT NULL /*PRIMARY KEY*/, 
  group_name    VARCHAR2 (150)   NOT NULL, 
  project_id    NUMBER   (15)    NOT NULL, 
  user_id       NUMBER   (15)    NOT NULL
 );

Then you do not need PL/SQL and can use:

INSERT INTO app_project_group (group_id, group_name, project_id, user_id)
SELECT group_id, group_name, TO_NUMBER(project_id), user_id
FROM   JSON_TABLE(
         :your_json,
         '$'
         COLUMNS(
           group_name VARCHAR2(150) PATH '$.group_name',
           group_id   NUMBER(15)    PATH '$.group_id',
           user_id    NUMBER(15)    PATH '$.user_id',
           NESTED PATH '$.projects[*]'
           COLUMNS (
             project_id VARCHAR2(15)  PATH '$.project.id'
           )
         )
       );

Then the table will contain:

GROUP_ID	GROUP_NAME	PROJECT_ID	USER_ID
10001	Grupo 1	721	12345
10001	Grupo 1	722	12345
10001	Grupo 1	723	12345
10001	Grupo 1	724	12345

db<>fiddle here