postgresql - 对json对象的键/值查询

Question

运行Postgres 9.6。

所以我有这个键/值查找表，它建立了一个巨大的JSON对象的最深的子值。鉴于这种结构的表：

CREATE TABLE myschema.file_items 
(
    id integer NOT NULL DEFAULT nextval('file_items_id_seq'::regclass), 
    file_id integer NOT NULL, 
    key character varying[] COLLATE pg_catalog."default" NOT NULL, 
    value character varying COLLATE pg_catalog."default", 
    status character varying COLLATE pg_catalog."default", 
    CONSTRAINT file_items_pkey PRIMARY KEY (id) 
) 
WITH (
    OIDS = FALSE 
) 
TABLESPACE pg_default; 

ALTER TABLE verification.file_items 
    OWNER to postgres; 

insert into file_items (file_id, key, value, status) 
values (1, '{"cogs","cog1","description"}', 'val1', 'approved'); 
insert into file_items (file_id, key, value, status) 
values (1, '{"cogs","cog1","cost"}', '100', null); 
insert into file_items (file_id, key, value, status) 
values (1, '{"cogs","cog1","window"}', '[-200,500]', 'not verified'); 
insert into file_items (file_id, key, value, status) 
values (1, '{"cogs","cog2","description"}', 'val2', 'approved'); 
insert into file_items (file_id, key, value, status) 
values (1, '{"cogs","cog2","cost"}', '200', null); 
insert into file_items (file_id, key, value, status) 
values (1, '{"cogs","cog2","window"}', '[-300,500]', null); 

insert into file_items (file_id, key, value, status) 
values (1, '{"widgets","widget1","description"}', 'wid1', 'approved'); 
insert into file_items (file_id, key, value, status) 
values (1, '{"widgets","widget1","cost"}', '100', 'approved'); 
insert into file_items (file_id, key, value, status) 
values (1, '{"widgets","widget1","window"}', '[-200,500]', 'not verified'); 
insert into file_items (file_id, key, value, status) 
values (1, '{"widgets","widget2","description"}', 'wid2', null); 
insert into file_items (file_id, key, value, status) 
values (1, '{"widgets","widget2","cost"}', '300', 'approved'); 
insert into file_items (file_id, key, value, status) 
values (1, '{"widgets","widget2","window"}', '[-1000,700]', null); 

我可以查询我所有的齿轮像这样：

select * 
from file_items 
where 'cogs' = any(key) 

我怎么会逆向工程这个目标？相反，我想以某种方式产生一个JSON对象格式如下：

"cogs": { 
    "cog1": { 
     "description": "val1", 
     "cost":100, 
     "window":[-200,500] 
    }, 
    "cog2": { 
     "description": "val2", 
     "cost":200, 
     "window":[-300,500] 
    } 
} 

请注意，我故意不想要做齿轮对象的数组。它们是齿轮对象的实际属性。这样做是因为我们可以有传入的json对象，我们不知道它的所有属性，因此我们使用键/值映射表来动态地识别这些属性值是什么（即我们不知道我们会事先知道一个“cog67”对象，或者该对象将贴上什么类型的属性....）。

由于此查询最终将从Node.js包（'pg'模块...）触发，如果我无法通过查询重新创建json对象，则可能需要在javascript中执行此操作本身。只是想知道是否可以在数据库级别正确构建json对象并返回它，而不是查询一堆行并重新构造服务器端代码中的对象。

任何帮助将不胜感激！谢谢！

Answer 1

使用jsonb_object_agg()两次，聚集的两个级别（jsonb_pretty()没有必要的，用于一个不错的输出）：

select jsonb_pretty(jsonb_build_object(key, jsonb_object_agg(subkey, value))) 
from (
    select key[1], key[2] as subkey, jsonb_object_agg(key[3], value) as value 
    from file_items 
    where 'cogs' = any(key) 
    group by key[1], key[2] 
    ) s 
group by key; 

      jsonb_pretty    
------------------------------------- 
{         + 
    "cogs": {      + 
     "cog1": {     + 
      "cost": "100",   + 
      "window": "[-200,500]",+ 
      "description": "val1" + 
     },       + 
     "cog2": {     + 
      "cost": "200",   + 
      "window": "[-300,500]",+ 
      "description": "val2" + 
     }       + 
    }        + 
} 
(1 row)