在JSON中搜索精确的字符串值

Question

我有一个存储在JSON中的列，看起来像

列名：s2s_payload

值：

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

我想查询数组中的精确值，而不是返回某个数据类型的所有值。我使用JSON_EXTRACT来获取不同的计数。

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')

如果我想过滤哪里的"事件类型"："搜索"，我该怎么做？

我尝试使用CAST(s2s_payload AS CHAR(="｛"eventtype"："search"｝"，但没有成功。

Answer 1

您需要使用json_extract+CAST来获得要与之进行比较的实际值：

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

或者与json_extract_scalar相同(因此不需要CAST(：

json_extract_scalar(s2s_payload, '$.eventtype')