0

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?

Aaron Maxwell
  • 31
  • 1
  • 1
  • 2

0 Answers0