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.
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.