Postgres alias select as column not found

Asked Sep 20 '17 at 16:27

Active Sep 20 '17 at 16:27

Viewed 122 times

I have a table listings which has a jsonb column mls_ids.

When I run

select jsonb_array_elements(mls_ids) as objects from listings where objects = '{}';

I receive this error:

ERROR:  column "objects" does not exist

I'm sure this is just basic SQL that I'm messing here but I haven't figured it out yet.

What I am trying to achieve in the query is return the rows that have an empty hash as an element in the array.

asked Sep 20 '17 at 16:27

wuliwong

You need to use a subquery, CTE, or lateral join to define the column or repeat the expression in the `where`. – Gordon Linoff Sep 20 '17 at 16:28
@GordonLinoff Yes, I just got it to work using the LATERAL method. I'm debating whether to answer the question myself or just delete it. – wuliwong Sep 20 '17 at 16:51
It's probably a good one to answer. – Chris Travers Sep 20 '17 at 17:50
You should have answered this :) – Vivek Nov 05 '20 at 10:54

0 Answers0