Search for exact string value in JSON

I have a column stored in JSON that looks like

column name: s2s_payload

Values:

{ 
    "checkoutdate":"2019-10-31",
    "checkindate":"2019-10-30",
    "numtravelers":"2",
    "domain":"www.travel.com.mx",
    "destination": {
                       "country":"MX",
                       "city":"Manzanillo"
                   },
    "eventtype":"search",
    "vertical":"hotels"
}

I want to query exact values in the array rather than returning all values for a certain data type. I was using JSON_EXTRACT to get distinct counts.

SELECT 
    COUNT(JSON_EXTRACT(s2s_payload, '$.destination.code')) AS total, 
    JSON_EXTRACT(s2s_payload, '$.destination.code') AS destination
FROM 
    "db"."events_data_json5_temp"
WHERE 
    id = '111000'
    AND s2s_payload IS NOT NULL
    AND yr = '2019'
    AND mon = '10'
    AND dt >= '26'
    AND JSON_EXTRACT(s2s_payload, '$.destination.code')
GROUP BY  
    JSON_EXTRACT(s2s_payload, '$.destination.code')

If I want to filter where “”eventtype””:””search”” how can I do this?

I tried using CAST(s2s_payload AS CHAR) = ‘{“eventtype””:””search”}’ but that didn’t work.

Answer

You need to use json_extract + a CAST to get actual value to compare against:

CAST(json_extract(s2s_payload, '$.eventtype') AS varchar) = 'search'

or, same with json_extract_scalar (and thus with no need for a CAST):

json_extract_scalar(s2s_payload, '$.eventtype')

Advertisement

Answer