如何在PostgreSQL中获取JSON数组

debugcn 发表于 Dev

努法

下面的代码是一个json参数。如何获取角色详细信息。我需要获取角色数组的ID。

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

我的书面职能如下。但我无法将数据插入到userroles表中。

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$
;

所以我需要将数据插入到userroles表中。

a_horse_with_no_name

您不需要任何循环。

第一个INSERT的SELECT错误，因为您传递的值不是数组。您只需要直接从传递的参数访问属性。由于这是单行，因此无需循环。

要插入角色，您也不需要循环，只需将其jsonb_array_elements()用作INSERT语句的源即可：

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;

我使用的jsonb不是json这里，因为涉及JSON时，这是推荐的数据类型。

在线示例

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。