Your Location is: Home > Postgresql-9.5

Can't filter by alias after CASE-WHEN. Column doesn't exist

From: Taiwan View: 4287 a_subscriber 

Question

column data has type jsonb

Here query that filter by phone number:

SELECT id, data,
CASE states->0->>'id'
    WHEN 'order.completed' THEN 'completed'
    ELSE 'progress'
    END AS order_state
FROM myTable
WHERE  data->'contacts'->'customer'->>'phone' LIKE '%123456%'

Nice. It's work fine.

But now I want also to filter by alias order_state.

I try this:

SELECT id, data,
CASE states->0->>'id'
    WHEN 'order.completed' THEN 'completed'
    ELSE 'progress'
    END AS order_state
FROM myTable
WHERE  data->'contacts'->'customer'->>'phone' LIKE '%123456%' AND order_state = 'progress'

But I get error:

ERROR:  column "order_state" does not exist
LINE 7: ...s'->'customer'->>'phone' LIKE '%123456%' AND order_stat...

Best answer

Your where condition has to be on the base value.

... WHERE ... AND states->0->>'id' <> 'order.completed';

which makes the CASE selection meaningless.