Фильтрация столбца PostgreSQL JSONB не работает

У меня есть следующий JSON, хранящийся в столбце типа jsonb. Я хочу запросить второй элемент внешнего массива и отфильтровать все строки, которые имеют значение в столбце «cid» как «CID1».

{"root":[[
            {"cid":"CID1","Display":"User One","FName":"User","LName":"One"},
            {"cid":"CID1","Display":"User Two","FName":"User","LName":"Two"},
            {"cid":"CID1","Display":"User Three","FName":"User","LName":"Three"},
            {"cid":"CID2","Display":"User One","FName":"User","LName":"One"},
            {"cid":"CID2","Display":"User Two","FName":"User","LName":"Two"},
            {"cid":"CID2","Display":"User Three","FName":"User","LName":"Three"}    

            ],
            [
            {"cid":"CID1","Display":"User One","FName":"Userfff","LName":"One"},
            {"cid":"CID1","Display":"User Two","FName":"User","LName":"Two"},
            {"cid":"CID1","Display":"User Three","FName":"User","LName":"Three"},
            {"cid":"CID2","Display":"User One","FName":"User","LName":"One"},
            {"cid":"CID2","Display":"User Two","FName":"User","LName":"Two"},
            {"cid":"CID2","Display":"User Three","FName":"User","LName":"Three"}    
            ]]}

Я написал следующий запрос и успешно извлек все строки из второго массива в виде объектов JSON, однако, когда я пытаюсь их отфильтровать, я получаю сообщение об ошибке

42703: столбец "filterin" не существует

select jsonb_array_elements((ARRAY(select jsonb_array_elements(msg->'root') ele ))[2]::jsonb) filterin 
from js
where filterin->>'cid'='CID1';

Что мне следует исправить в следующем запросе, чтобы я мог фильтровать столбцы?


postgresql jsonb
person puneet    schedule 29.01.2017    source источник
comment
Сработало отлично, спасибо @Sami   -  person puneet    schedule 29.01.2017
comment
@ Сами, как вы думаете, было бы лучше добавить индекс GIN в столбец jsonb?   -  person puneet    schedule 29.01.2017

Ответы (1)


arrow_upward
1
arrow_downward

Выходные псевдонимы нельзя использовать в части WHERE. У вас есть два варианта: подзапрос или дублирование определения.

select jsonb_array_elements((ARRAY(select jsonb_array_elements(msg->'root') ele ))[2]::jsonb) filterin
from js
where jsonb_array_elements((ARRAY(select jsonb_array_elements(msg->'root') ele ))[2]::jsonb)->>'cid'='CID1';

or

SELECT filterin FROM 
(select jsonb_array_elements((ARRAY(select jsonb_array_elements(msg->'root') ele ))[2]::jsonb) filterin
from js) data
WHERE filterin->>'cid'='CID1';
person Sami Kuhmonen    schedule 29.01.2017