function - how to fetch json array in postgresql

Question

The below code is a json parameter. how to fetch roles details. i need to fetch id of roles array.

'{
            "id": 1,
            "firstName": "test",
            "lastName": "user",
            "email": "[email protected]",
            "roles": [
                {
                    "id": 2,
                    "roleName": "DBUser"
                },
                {
                    "id": 1,
                    "roleName": "Admin"
                }
            ]
        }'::json

My written function is given below. but i can't insert data into userroles table.

CREATE OR REPLACE FUNCTION public.Save_User(json_data json)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
    v_id integer;
i json;
BEGIN

    FOR i IN
    SELECT *
    FROM json_array_elements(json_data) loop
    INSERT INTO users (firstname, lastname, email)
    VALUES (i ->>'FirstName',
            i ->>'LastName',
            i ->>'Email') RETURNING id
    INTO v_id;
--for roles
    FOR i IN
    SELECT *
    FROM json_array_elements(json_data->'roles') loop
    INSERT INTO userroles (userid, roleid) 
    VALUES (v_id,
            i ->>'Id');
    END LOOP;
--end
END LOOP;
RETURN v_id;
END;
$function$
;

So i need to insert data to userroles table.

question from:https://stackoverflow.com/questions/66061042/how-to-fetch-json-array-in-postgresql

Answer 1

You don't need any loops for this.

The SELECT for the first INSERT is wrong, because the value you are passing isn't an array. You only need to access the properties directly from the passed parameter. As this is a single row, no looping is required to begin with.

To insert the roles, you also don't need a loop, just use jsonb_array_elements() as the source for the INSERT statement:

create function save_user(p_user_data jsonb)
  returns int
as
$$
declare
  v_id int;
begin
  insert into users (firstname, lastname, email)
  select p_user_data ->> 'firstName', 
         p_user_data ->> 'lastName',
         p_user_data ->> 'email'
  returning id
  into v_id;
    
  insert into userroles (userid, roleid)
  select v_id, (r.item ->> 'id')::int
  from jsonb_array_elements(p_user_data -> 'roles') as r(item);

  return v_id;
end;  
$$
language plpgsql;

I used jsonb rather than json here, because that's the recommended data type when JSON is involved.

Online example