postgres如何迭代嵌套的json对象

klr1opcd 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(411)

我有一个对象如下。我想迭代insert中的每个用户，并将它们插入到我的数据库中。我被困在如何到达内部物体。我有下面这样的代码，我被困在如何进一步进入obejct，它只得到 insert , delete 以及 update . 我用的是postgresql。
我懂了 ->> int Get JSON array element as text '[1,2,3]'::json->>2 在文档中，但不知道如何合并到我的代码中

DECLARE
   _key   text;
   _value text;
BEGIN
    FOR _key, _value IN
       SELECT * FROM jsonb_each_text($1)
    LOOP
       RAISE NOTICE '%: %', _key, _value;
    END LOOP;
    RETURN input;
END
 {
      insert: {
        jsmith:
        { 
          name: 'John Smith',
          mail: 'JSmith@smith.com',
          jobTitle: 'Lead',
          hasImage: true,
          teamId: '35' 
        },
        lmark:
        {
          name: 'Laurendy Mark',
          mail: 'LMark@mark.com',
          jobTitle: 'System Admin',
          hasImage: true,
          teamId: '40'
        }
      },
      delete: {
        lbeth
        {
          name: 'Lisa Beth',
          mail: 'LBeth@smith.com',
          jobTitle: 'Assistant Director',
          hasImage: true,
          teamId: '40',
          uid: '200'
        }
      },
      update: {}
    }

sql postgresql JSON

来源：https://stackoverflow.com/questions/62027727/postgres-how-to-iterate-over-nested-json-object

1条答案

按热度按时间

ttisahbt1#

对此，您并不真正需要pl/pgsql函数。这可以使用数据修改cte来完成

with input (col) as (
  values ('
    {
       "insert":{ 
        "jsmith":{"name":"John Smith", "mail":"JSmith@smith.com","jobTitle":"Lead","hasImage":true,"teamId":35},
        "lmark":{"name":"Laurendy Mark","mail":"LMark@mark.com","jobTitle":"System Admin","hasImage":true,"teamId":40}
       },
       "delete":{
         "lbeth":{"name":"Lisa Beth","mail":"LBeth@smith.com","jobTitle":"Assistant Director","hasImage":true,"teamId":40,"uid":200}
       },
       "update":{}
    }'::jsonb)
), do_insert as (
  insert into the_table (name, mail, job_title, has_image, team_id)
  select i.value ->> 'name', 
         i.value ->> 'mail',
         i.value ->> 'jobTitle',
         (i.value ->> 'hasImage')::boolean,
         (i.value ->> 'teamId')::int
  from input
   cross join jsonb_each(col -> 'insert') i
  returning *
), do_delete as (
  delete from the_table
  where uid in (select (i.value ->> 'uid')::int
                 from input
                   cross join jsonb_each(col -> 'delete') i)
)                   
update the_table
  set has_image = (i.value ->> 'hasImage')::boolean
from input
 cross join jsonb_each(col -> 'update') i
where (i.value ->> 'uid')::int = the_table.uid
;

在线示例

展开查看全部

赞(0）回复(0）举报 2021-07-26

我来回答

postgres如何迭代嵌套的json对象

1条答案

相关问题

热门标签

最新问答