json - 如何使用 pl/pgsql 将具有动态元素名称的 JSON 数据转换为行？

Question

我需要从 pl/pgsql 函数中的 restful 服务获取 json。 (我无法控制restful webservice。它是由其他人发布的)。我可以获得 json，但无法将其转换为行(用于插入到表中)。它的简化格式如下。每个 GUID 都是随机的，我以前不知道它们的内容。当我从文本(使用::json)转换它时，我没有收到任何错误，因此它是有效的 json。

    l_json := '{"GUID-0001":{"Id":"1","Field1":"aaa1","Field2":"bbb1"}, "GUID-0002":{"Id":"2","Field1":"aaa2","Field2":"bbb2"}}'::json;

我尝试了几个 Postgresql json 函数，但每次都出现不同的错误。即，当我使用 json_array_elements() 函数时，出现“错误:无法在非数组上调用 json_array_elements”。当我尝试 json_each_text() 函数时，我得到“错误:查询没有结果数据的目的地”

我需要如下结果集:

GUID     | Id | Field1 | Field2
---------+----+--------+-------
GUID-0001| 1  |   aaa1 |   bbb1
GUID-0002| 1  |   aaa2 |   bbb2

Answer 1

您可以使用 jsonb_object_keys() 获取所有键，并使用它来访问 JSON 中的字段:

with data(doc) as (
  values ('{"GUID-0001":{"Id":"1","Field1":"aaa1","Field2":"bbb1"}, "GUID-0002":{"Id":"2","Field1":"aaa2","Field2":"bbb2"}}'::jsonb)
)
select t.uid, 
       d.doc -> t.uid ->> 'Id' as id,
       d.doc -> t.uid ->> 'Field1' as column1,
       d.doc -> t.uid ->> 'Field2' as column2
from data d, jsonb_object_keys(doc) as t(uid);

返回:

uid       | id | column1 | column2
----------+----+---------+--------
GUID-0001 | 1  | aaa1    | bbb1   
GUID-0002 | 2  | aaa2    | bbb2   

您可以将其放入接受 jsonb 作为参数的函数中:

create or replace function store_json(p_doc jsonb)
  returns void
as
$$
  insert into the_table (guid, id, column1, column2)
  select t.uid, 
         (d.doc -> t.uid ->> 'Id')::int,
         d.doc -> t.uid ->> 'Field1',
         d.doc -> t.uid ->> 'Field2'
  from (select p_doc) as d(doc), 
       jsonb_object_keys(doc) as t(uid);
$$
language sql;