Запрос Postgres jsonb для вложенного объекта

Моя версия базы данных postgres: 9.4.4. и у меня есть таблица с такой структурой;

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

У меня есть такие записи в столбце «данные»;

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

Мне нужно извлечь все записи для "ячейки". Ожидаемая запись будет

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

или для "электронной почты" ["[email protected]","[email protected]"]

Моим лучшим усилием ниже был двухэтапный процесс, и он не будет масштабироваться для x без «нестандартных» объектов;

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"

Буду очень признателен, если меня направят в правильном направлении


postgresql jsonb
person BlowMan    schedule 14.09.2015    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Используйте 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

Вы можете объединить два внутренних подзапроса:

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

Сгруппировать результаты по productid:

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
person klin    schedule 14.09.2015
comment
очень благодарен, вы определенно указали мне туда, будет читать по этим ссылкам. Можете ли вы обновить свой запрос для группировки по идентификатору продукта, чтобы для каждого идентификатора продукта была связанная ячейка - person BlowMan; 14.09.2015
comment
Вау, супербыстро, супер клин!! - person BlowMan; 14.09.2015