json - postgres JSON 数据

Question

我无法掌握如何在 PG 中使用 JSON。我有一个相当大的表(~4M 行)，其中有一个 JSONB 列，其中包含一个数组
零到几百“行”，每行都有几个属性。我相信这是数据的合理近似:

drop table temp_jd;
create table temp_jd (id serial,d jsonb);
insert into temp_jd (d) values 
    (
        '[
            {"thing":"v1","a1":"bla"},
            {"thing":"v2","a1":"blaugh"},
            {"otherthing":"v1","a1":"something"}
        ]'
    ),
    (
        '[
            {"thing":"v12","a12":"bla"},
            {"thing":"v2","a1":"blaugh"},
            {"morething":"v1","a1":"whatever"}
        ]'
    )
;

我最终想通过各种位-n-块进行查询，并提取聚合文本，这样我就可以假装我有“列”，例如

thing值 (v1; v2) 和 (v12; v2)，或

thing_a1值 (bla; blaugh) 和 (bla; blaugh)

我可以提取值

select id,jsonb_array_elements(d)->'thing' as thingval from temp_jd;
id | thingval 
----+----------
  1 | "v1"
  1 | "v2"
  1 | 
  2 | "v12"
  2 | "v2"
  2 | 

但我不知道如何将它们聚合为字符串。
我可以执行基本的查询操作

select id, jsonb_array_elements(d)->>'morething' from temp_jd where d @> '[{"morething":"v1"}]';

 id | ?column? 
----+----------
  2 | 
  2 | 
  2 | v1

但我不知道怎么去 key='thing' and thing.a1='blaugh'或 key='thing' and thing.a1 LIKE 'blaugh%'任何有助于更好地理解这一点的帮助将不胜感激。

Answer 1

看看 this fiddle有助于阐明您可以做什么。
这个查询爆了你的jsonb对象的位置，您可以在单独的行中以列的形式访问对象的键和值:

select t.id, a.element, a.ind - 1 as array_index, o.key, o.value
  from temp_jd t
 cross join lateral jsonb_array_elements(t.d) 
                      with ordinality as a(element, ind)
 cross join lateral jsonb_each(a.element) as o(key, value)
 order by t.id, a.ind, o.key; 

要对此类似于您的示例执行聚合:

with blowup as (
  select t.id, a.element, a.ind - 1 as array_index, o.key, o.value
    from temp_jd t
   cross join lateral jsonb_array_elements(t.d) 
                        with ordinality as a(element, ind)
   cross join lateral jsonb_each(a.element) as o(key, value)
   order by t.id, a.ind, o.key
)
select id, 
       array_agg(value order by array_index) as things
  from blowup 
 where key = 'thing'
 group by id;