我有一个存储在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”}',但这无效。
您需要使用json_extract
+ aCAST
来获取要与之进行比较的实际值:
CAST(json_extract(s2s_payload, '$.eventtype') AS varchar) = 'search'
或者,与相同json_extract_scalar
(因此不需要CAST
):
json_extract_scalar(s2s_payload, '$.eventtype')
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句