0

I'm trying to search for a string in a JSON column type in my database along with searching between two dates, I'm getting this error telling me that my column is unknown when it clearly isn't because I'm setting it using AS.

My JSON column is data, and inside of there I'm searching for a string and pulling that out with a certain value which I'm then searching by:

SELECT created_at,
CONCAT("", SUBSTRING(data, LOCATE('my_field', data)+13, 20) ) AS my_field_as_column
FROM applications
WHERE created_at BETWEEN "2021-03-17 00:00:00" AND "2021-03-17 23:59:59"
AND my_field_as_column LIKE '%hello world%'

Why would I get:

Unknown column 'my_field_as_column' in 'where clause'

Ryan H
  • 2,620
  • 4
  • 37
  • 109
  • 1
    The WHERE clause is evaluated before the column alias is created. – jarlh Mar 17 '21 at 16:10
  • Tag your question with the database you are using. But the answer to your question is that a column alias cannot be used in the `FROM` or `WHERE` clauses or again in the same `SELECT`. Typical work-arounds are CTEs, subqueries, or lateral joins. – Gordon Linoff Mar 17 '21 at 16:10
  • I've tagged the question, and tried moving the dates as the last thing, no luck with that – Ryan H Mar 17 '21 at 16:11
  • As @jarlh says _my_field_as_column_ is not a column in your table, is an alias. In the where clause you can only use column names – nacho Mar 17 '21 at 16:13
  • How do I use a sub query then, yes it's not a column, I need to make it a column for my SQL search – Ryan H Mar 17 '21 at 16:15
  • just add `CONCAT("", SUBSTRING(data, LOCATE('my_field', data)+13, 20) ) BETWEEN "2021-03-17 00:00:00" AND "2021-03-17 23:59:59" `to your where clause, instead of using the alias. – Andrew Mar 17 '21 at 16:58
  • Are you suggesting to have: `WHERE created_at BETWEEN date AND date AND CONCAT("", SUBSTRING(data, LOCATE('my_field', data)+13, 20) )` because `created_at` isn't the same as the field – Ryan H Mar 18 '21 at 08:46

0 Answers0