I'm a bit stumped getting an SQL query to work, using Postgress 9.5. Here is the table schema:
dbhost-> \d item
Table "public.item"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------------------------
id | integer | not null default nextval('items_id_seq'::regclass)
emails | jsonb | not null
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"items_emails_key" UNIQUE CONSTRAINT, btree (emails)
In that jsonb field, "emails", all entries will encode a list of strings. Something like ["alpha@example.com", "beta@example.com"]
. And I want to find all rows whose FIRST element in the list starts with a particular string prefix.
I thought this query would work:
select
cast(emails::json->0 as text) as entry
from item
where entry like 'some_prefix%'
limit 5;
But I get this error:
ERROR: column "entry" does not exist
LINE 4: where entry like 'some_prefix%'
This must be a basic syntax error on my part. Can anyone see what I missed?