Postgres using named column in where clause

0
votes

In Postgres I'm struggling with this syntax. It works in mysql but not sure what I'm doing wrong is.

So let's say I have a json document. I want to select a column in that document and return the result as "text"

So my query would look like this.

SELECT member_id, data->>'username' AS username
FROM player.player

Returns this as expected.

result

Now lets say I want to select a name from the column so my query would look like this.

SELECT member_id, data->>'username' AS username
FROM player.player WHERE username LIKE 'sam'

When I run the query I get this.

result 2'

Why does it do that? The json I'm returning is returning as text data type since I'm using json->> on a column.

1
postgresql

1 Answers

0
votes

PostgreSQL is based on SQL standard, and there are not possible to use a alias on same query level. You should to use derived table and filter on higher level:

postgres=# select 1 as x where x = 1;
ERROR:  column "x" does not exist
LINE 1: select 1 as x where x = 1;
                        ^
postgres=# select * from (select 1 as x) s where x = 1;
┌───┐
│ x │
╞═══╡
│ 1 │
└───┘
(1 row)