DROP FUNCTION JSON_SQUASH;
CREATE FUNCTION JSON_SQUASH(name string)
RETURNS string
BEGIN
DECLARE res STRING;
SET res = REPLACE(REPLACE(REPLACE(name, '[\"', ''), '\"]', ''), '"', '');
IF (res = '[]') THEN
SET res = REPLACE(res, '[]', '<no data>');
END IF;
RETURN res;
END;
SELECT
recv_date,
recv_time,
JSON_SQUASH(hostname) AS hostname
FROM
(SELECT
a.recv_date AS recv_date,
a.recv_time AS recv_time,
json.filter(json_data, '$.http.host') AS hostname
FROM http_traffic_json a JOIN http_traffic_ua b
ON (a.guid = b.guid)) AS origin WHERE recv_time BETWEEN CURTIME() - 300 AND CURTIME();