嵌套对象上的Postgres jsonb查询 [英] Postgres jsonb query on nested object

问题描述

我的postgres数据库版本:9.4.4.我有一个具有这种结构的表；

My postgres db version: 9.4.4. and I have a table with this structure;

CREATE TABLE product_cust
(
 productid character(2),
  data jsonb,
)

我在数据"列中有这样的记录；

I have records like this in the "data" column;

{"productid":"01","cust": [
        {"cell": ["0000xxx0", "0000xxx1"], "name": "John", "email": ["[e-mail]"], "custtype": "c"}, 
        {"cell": ["0000xxx2", "0000xxx3"], "name": "Smith", "email": ["[e-mail]"], "custtype": "c"}  
]}

我需要提取"cell"的所有记录.预期记录将是

I need to extract all records for "cell" . Expected record will be

["0000xxx0", "0000xxx1","0000xxx2", "0000xxx3"] 

或电子邮件" ["[e-mail]","[e-mail]"]

下面我最大的努力是一个两步(2)的过程，并且不会针对x个受约束"的对象进行缩放；

My best effort below has been a two(2) step process and will not scale for x no of "cust" objects;

select (data::json#>'{cust,0}')::json#>'{cell}' from product_cust; //return "0000xxx0", "0000xxx1"
select (data::json#>'{cust,1}')::json#>'{cell}' from product_cust; //return "0000xxx2", "0000xxx3"

如果能指出正确的方向，我将不胜感激

I will be most grateful if i can be pointed in the right direction

推荐答案

使用 json_agg()和 jsonb_array_elements()函数:

select json_agg(cell)
from (
    select jsonb_array_elements(elem->'cell') cell
    from (
        select jsonb_array_elements(data->'cust') elem
        from product_cust
        ) subsub
    ) sub

您可以合并两个内部子查询:

You can merge two inner subqueries:

select json_agg(cell)
from (
    select jsonb_array_elements(jsonb_array_elements(data->'cust')->'cell') cell
    from product_cust
    ) sub

按产品ID分组结果:

select productid, json_agg(cell)
from (
    select productid, jsonb_array_elements(jsonb_array_elements(data->'cust')->'cell') cell
    from product_cust
    ) sub
group by 1
order by 1

这篇关于嵌套对象上的Postgres jsonb查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！