postgresql-查询以建立json [英] postgresql - query to build up json
问题描述
运行:PostgreSQL 9.6.2
Running: PostgreSQL 9.6.2
我将数据存储在键/值对形式的表中. 键"实际上是一个json对象的路径，每个对象都是一个属性.因此，例如，如果键为"cogs"，"props1"，"value"，则json对象将如下所示:
I have data stored in a table that is in the form of a key/value pair. The "key" is actually the path of a json object, each one being a property. So for example if the key was "cogs","props1","value", then the json object would be like so:
{
"cogs":{
"props1": {
"value": 100
}
}
}
如果可能的话，我想通过SQL查询以某种方式重构json对象.这是测试数据集:
I'd like to somehow reconstruct a json object via a SQL query if possible. Here is the test data set:
drop table if exists test_table;
CREATE TABLE test_table
(
id serial,
file_id integer NOT NULL,
key character varying[],
value character varying,
status character varying
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","description"}', 'some awesome cog', 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","display"}', 'Giant Cog', null);
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props1","value"}', '100', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props1","id"}', 26, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props1","dimensions"}', '{"200", "300"}', null);
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props2","value"}', '200', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props2","id"}', 27, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props2","dimensions"}', '{"700", "800"}', null);
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","description"}', 'some awesome widget', 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","display"}', 'Giant Widget', null);
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props1","value"}', '100', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props1","id"}', 28, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props1","dimensions"}', '{"200", "300"}', null);
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props2","value"}', '200', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props2","id"}', 29, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props2","dimensions"}', '{"900", "1000"}', null);
我正在寻找的输出格式如下:
The output I'm looking for is in this format:
{
"cogs": {
"description": "some awesome cog",
"display": "Giant Cog",
"props1": {
"value": 100,
"id": 26,
"dimensions": [200, 300]
},
"props2": {
"value": 200,
"id": 27,
"dimensions": [700, 800]
}
},
"widgets": {
"description": "some awesome widget",
"display": "Giant Widget",
"props1": {
"value": 100,
"id": 28,
"dimensions": [200, 300]
},
"props2": {
"value": 200,
"id": 29,
"dimensions": [900, 1000]
}
}
}
我面临的一些问题:
-
值"列可以容纳文本，数字和数组.无论出于何种原因，使用knex.js的服务器端代码都将整数数组(即[100,300])存储为postgres，格式如下:{"100"，"300"}.我还需要确保将其提取为整数数组.
The "value" column can hold text, numbers, and an array. For whatever reason, the server-side code using knex.js is storing an array of integers (ie, [100,300]) into postgres as the following format: {"100","300"}. I need to ensure I extract this out as an array of integers as well.
尝试使其尽可能动态.也许是一个递归过程来找出键"路径的深度....而不是对数组查找值进行硬编码.
Trying to make this dynamic as possible. Maybe a recursive procedure to figure out what depth of the "key" path exists.... rather than hard-coding array lookup values.
json_object_agg可以很好地将属性组合到一个对象中.但是，当达到空值时，它会中断.因此，如果键"列只有两个值(即"cogs"，"description")，而我尝试汇总长度为三的数组(即"cogs"，"props1"，"value")，除非我仅对长度为3的数组进行过滤，否则它将破坏.
json_object_agg works well to group together properties into a single object. However it breaks when hitting a null value. So if the "key" column has only two values (ie, "cogs","description"), and I attempt to aggregate up an array of length three (ie, "cogs","props1","value"), it will break unless I filter on only arrays of length 3.
保留输入的顺序.下面的@klin解决方案非常了不起，让我有95％的解决方法.但是我没有提到也要保留顺序...
Preserve the ordering of the input. @klin solution below is amazing and gets me 95% of the way there. However I failed to mention to also preserve the ordering...
推荐答案
动态解决方案需要一些工作.
A dynamic solution needs some work.
首先，我们需要一个函数来将文本数组和值转换为jsonb对象.
First, we need a function to convert a text array and a value to a jsonb object.
create or replace function keys_to_object(keys text[], val text)
returns jsonb language plpgsql as $$
declare
i int;
rslt jsonb = to_jsonb(val);
begin
for i in select generate_subscripts(keys, 1, true) loop
rslt := jsonb_build_object(keys[i], rslt);
end loop;
return rslt;
end $$;
select keys_to_object(array['key', 'subkey', 'subsub'], 'value');
keys_to_object
------------------------------------------
{"key": {"subkey": {"subsub": "value"}}}
(1 row)
接下来，是另一个合并jsonb对象的功能(请参见在PostgreSQL中合并JSONB值).
Next, another function to merge jsonb objects (see Merging JSONB values in PostgreSQL).
create or replace function jsonb_merge(a jsonb, b jsonb)
returns jsonb language sql as $$
select
jsonb_object_agg(
coalesce(ka, kb),
case
when va isnull then vb
when vb isnull then va
when jsonb_typeof(va) <> 'object' or jsonb_typeof(vb) <> 'object' then vb
else jsonb_merge(va, vb) end
)
from jsonb_each(a) e1(ka, va)
full join jsonb_each(b) e2(kb, vb) on ka = kb
$$;
select jsonb_merge('{"key": {"subkey1": "value1"}}', '{"key": {"subkey2": "value2"}}');
jsonb_merge
-----------------------------------------------------
{"key": {"subkey1": "value1", "subkey2": "value2"}}
(1 row)
最后，让我们基于以上函数创建一个聚合，
Finally, let's create an aggregate based on the above function,
create aggregate jsonb_merge_agg(jsonb)
(
sfunc = jsonb_merge,
stype = jsonb
);
我们完成了:
select jsonb_pretty(jsonb_merge_agg(keys_to_object(key, translate(value, '{}"', '[]'))))
from test_table;
jsonb_pretty
----------------------------------------------
{ +
"cogs": { +
"props1": { +
"id": "26", +
"value": "100", +
"dimensions": "[200, 300]" +
}, +
"props2": { +
"id": "27", +
"value": "200", +
"dimensions": "[700, 800]" +
}, +
"display": "Giant Cog", +
"description": "some awesome cog" +
}, +
"widgets": { +
"props1": { +
"id": "28", +
"value": "100", +
"dimensions": "[200, 300]" +
}, +
"props2": { +
"id": "29", +
"value": "200", +
"dimensions": "[900, 1000]" +
}, +
"display": "Giant Widget", +
"description": "some awesome widget"+
} +
}
(1 row)
这篇关于postgresql-查询以建立json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
