Filtering on PostgreSQL JSONB column doesn't work

Question

I have the following JSON stored in column of type jsonb. I want to query the second element of the outer array and filter all rows that have the value in 'cid' column as '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"}    
            ]]}

I have written the following query, and was successful in retrieving all the rows from second array as JSON objects, however when I try to filter them I get the error

42703: column "filterin" does not exist

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

What should I correct in the following query so that I am able to filter on columns?

@Sami, Do you think putting a GIN index on the jsonb column would be better? — puneet

Sami Kuhmonen Sami Kuhmonen · Accepted Answer · 2017-01-29T16:08:34

Output aliases cannot be used in the WHERE part. You have two choices: subquery or duplicate the definition.

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';

Filtering on PostgreSQL JSONB column doesn't work

1 Answers