Left side of LIKE expression must evaluate to a varchar?

user_7103494 0
Viewed 10659

I'm writing a query with a filter based on a string inside the field. However and I'm running the query it shows the error:

Left side of LIKE expression must evaluate to a varchar

I want to fFilter by everyone who bought a BMW based on a custom_field.

This is the sample data that is inside the custom field:

{ "car_model;":"BMWZ4", "car_id":"0980291", "manufacture":"bmw" }

Code:

SELECT
    first_name,
    Last_name,
    country,
    custom_field
FROM 
    sales_table
WHERE 
    custom_field LIKE '%BMWZ4%'

Expected result

first_name: John   
last_name: Parker      
country: Germany   
custom_field: {"car_model;":"BMWZ4","car_id":"098222291","brand":"bmw"}
sql-like sql
2 Answers
SELECT
first_name,
Last_name,
country,
custom_field
FROM sales_table
WHERE cast( custom_field as varchar(max)) LIKE '%BMWZ4%'
user_31799570

Your column custom_field is not a random string. It is specifically a JSON string. In fact, it might not be stored as a string at all, using a binary format called JSONB.

Databases that support JSON (which nowadays is pretty much all the major ones) all have bespoke JSON functions. You should be using these functions to extract patterns and values.

Often, treating the JSON column as its proper type results in more efficient queries than treating it as a string. Some databases even permit indexes on JSON columns.

user_11440350
Related