将扁平的密钥/值表转换为PostgreSQL中的层次JSON

i有一个带有唯一键/值对的postgresql表，该表最初是以JSON格式进行的，但已被归一化和融化：

key             | value
-----------------------------
name            | Bob
address.city    | Vancouver
address.country | Canada

我需要将其变成层次结构的JSON：

{
"name": "Bob",
"address": {
    "city": "Vancouver",
    "country": "Canada"
    }
}

有没有办法在SQL？

中轻松执行此操作

jsonb_set()几乎为您做了一切，但不幸的是，它只能创建缺失的叶子（即，在路径上缺少最后一个键），但不会全部缺少的分支。为了克服这一点，这是它的修改版本，它可以在任何丢失的级别上设置值：

create function jsonb_set_rec(jsonb, jsonb, text[])
  returns jsonb
  language sql
as $$
  select case
    when array_length($3, 1) > 1 and ($1 #> $3[:array_upper($3, 1) - 1]) is null
    then jsonb_set_rec($1, jsonb_build_object($3[array_upper($3, 1)], $2), $3[:array_upper($3, 1) - 1])
    else jsonb_set($1, $3, $2, true)
  end
$$;

现在，您只需要以一个空的JSON对象：{}一一将此函数应用于行。您可以使用任何一个递归CTE进行此操作：

with recursive props as (
   (select   distinct on (grp)
             pk, grp, jsonb_set_rec('{}', to_jsonb(value), string_to_array(key, '.')) json_object
    from     eav_tbl
    order by grp, pk)
  union all
   (select   distinct on (grp)
             eav_tbl.pk, grp, jsonb_set_rec(json_object, to_jsonb(value), string_to_array(key, '.'))
    from     props
    join     eav_tbl using (grp)
    where    eav_tbl.pk > props.pk
    order by grp, eav_tbl.pk)
)
select   distinct on (grp)
         grp, json_object
from     props
order by grp, pk desc;

或，定义为：

create aggregate jsonb_set_agg(jsonb, text[]) (
  sfunc    = jsonb_set_rec,
  stype    = jsonb,
  initcond = '{}'
);

您的查询可能变得很简单：

select   grp, jsonb_set_agg(to_jsonb(value), string_to_array(key, '.'))
from     eav_tbl
group by grp;

https://rextester.com/tulnu73750

没有准备好使用工具为此。该函数基于路径生成层次JSON对象：

create or replace function jsonb_build_object_from_path(path text, value text)
returns jsonb language plpgsql as $$
declare
    obj jsonb;
    keys text[] := string_to_array(path, '.');
    level int := cardinality(keys);
begin
    obj := jsonb_build_object(keys[level], value);
    while level > 1 loop
        level := level- 1;
        obj := jsonb_build_object(keys[level], obj);
    end loop;
    return obj;
end $$;

您还需要此答案中描述的汇总函数jsonb_merge_agg(jsonb)。查询：

with my_table (path, value) as (
values
    ('name', 'Bob'),
    ('address.city', 'Vancouver'),
    ('address.country', 'Canada'),
    ('first.second.third', 'value')
)
select jsonb_merge_agg(jsonb_build_object_from_path(path, value))
from my_table;

给出此对象：

{
    "name": "Bob",
    "first":
    {
        "second":
        {
            "third": "value"
        }
    },
    "address":
    {
        "city": "Vancouver",
        "country": "Canada"
    }
}

该功能不识别JSON数组。

我真的不能想到一些简单的东西，尽管我认为应该有一种更简单的方法。

我假设还有一些其他列可以用来将一个属于一个"人"的密钥一起使用，我在示例中使用了p_id。

select p_id, 
       jsonb_object_agg(k, case level when 1 then v -> k else v end) 
from (
  select p_id, 
         elements[1] k, 
         jsonb_object_agg(case cardinality(elements) when 1 then ky else elements[2] end, value) v, 
         max(cardinality(elements)) as level
  from (       
    select p_id, 
           "key" as ky, 
           string_to_array("key", '.') as elements, value
    from kv 
  ) t1
  group by p_id, k
) t2
group by p_id;

最内向的查询只是将点符号转换为数组，以便以后更容易访问。

下一个级别然后根据"键"构建JSON对象。对于"单级"键，它仅使用键/值，对于其他键，它使用第二个元素值，然后将属于属于的键进行汇总。

第二查询级别返回以下内容：

p_id | k       | v                                          | level
-----+---------+--------------------------------------------+------
   1 | address | {"city": "Vancouver", "country": "Canada"} |     2
   1 | name    | {"name": "Bob"}                            |     1
   2 | address | {"city": "Munich", "country": "Germany"}   |     2
   2 | name    | {"name": "John"}                           |     1

在第二步中完成的聚合，对于"单个元素"键留下了一个级别，这就是我们需要的级别。

如果没有进行区分，最终的聚合将返回{"name": {"name": "Bob"}, "address": {"city": "Vancouver", "country": "Canada"}}而不是通缉：{"name": "Bob", "address": {"city": "Vancouver", "country": "Canada"}}。

表达式case level when 1 then v -> k else v end本质上将{"name": "Bob"}转到"Bob"。

如此，使用以下示例数据：

create table kv (p_id integer, "key" text, value text);
insert into kv
values
(1, 'name','Bob'),
(1, 'address.city','Vancouver'),
(1, 'address.country','Canada'),
(2, 'name','John'),
(2, 'address.city','Munich'),
(2, 'address.country','Germany');

然后查询返回：

p_id | jsonb_object_agg                                                      
-----+-----------------------------------------------------------------------
   1 | {"name": "Bob", "address": {"city": "Vancouver", "country": "Canada"}}
   2 | {"name": "John", "address": {"city": "Munich", "country": "Germany"}}

在线示例：https：//rextester.com/sjotcd7977

create table kv (key text, value text);

insert into kv
values
('name','Bob'),
('address.city','Vancouver'),
('address.country','Canada'),
('name','John'),
('address.city','Munich'),
('address.country','Germany');

create view v_kv as select  row_number() over() as nRec, key, value from kv;

create view v_datos as
    select k1.nrec, k1.value as name, k2.value as address_city, k3.value as address_country
    from v_kv k1 inner join v_kv k2 on (k1.nrec + 1 = k2.nrec)
        inner join v_kv k3 on ((k1.nrec + 2= k3.nrec) and (k2.nrec + 1 = k3.nrec))
    where mod(k1.nrec, 3) = 1;

select json_agg(json_build_object('name',name, 'address', json_build_object('city',address_city, 'country', address_country)))
    from  v_datos;

相关内容

最新更新

热门标签：