mysql嵌套json列搜索并与今天进行比较

f4t66c6m 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(372)

我使用的是MySQL5.7.28版本。我有如下json数据。

CREATE TABLE `week2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(1),
  `json` text ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO week2(id,type,json)
VALUES
    (121,1,'[{"weekdays":"Sunday"},{"weekdays":"Monday"},{"weekdays":"Tuesday"},{"weekdays":"Wednesday"},{"weekdays":"Thursday"},{"weekdays":"Friday"},{"weekdays":"Saturday"}]'),
    (122,1,'[{"weekdays":"Sunday"},{"weekdays":"Monday"}]'),
    (123,2,'[{"start_time":"08:00 AM","end_time":"10:00 PM"}]');

如您所见，json列包含嵌套的json数据。所以在这里我想比较一下今天（星期六，我们现在处于开始时间和结束时间之间）

预期结果：

(121,1,'[{"weekdays":"Sunday"},{"weekdays":"Monday"},{"weekdays":"Tuesday"},{"weekdays":"Wednesday"},{"weekdays":"Thursday"},{"weekdays":"Friday"},{"weekdays":"Saturday"}]'),
(123,2,'[{"start_time":"08:00 AM","end_time":"10:00 PM"}]');

sql mysql JSON json-extract mysql-5.7

来源：https://stackoverflow.com/questions/62607117/mysql-nested-json-column-search-and-compare-to-with-todays-day

2条答案

按热度按时间

b91juud31#

您需要检测日期名称（1）和时间段（2），并在末尾使用or运算符将这两个条件组合起来。
for（1）：使用 DAYNAME() 函数，并使用 JSON_CONTAINS 功能。
对于（2）： TIME() 功能和 CAST 将字符串绑定到 TIME 数据类型可能与一个技巧一起使用，为 PM 键入时间。
因此，考虑使用：

SELECT *
  FROM `week2`
 WHERE JSON_CONTAINS(`json`->>'$[*].weekdays', CONCAT('"',DAYNAME( NOW() ),'"')) = 1
    OR
    (
      TIME(ADDTIME(NOW(),"8:00:00")) >=       
      CASE WHEN INSTR(REPLACE(`json`->>'$[0].start_time',"12:00 AM","00:00 AM"),"PM")>0 
           THEN 
                CAST(CONCAT(MOD((TIME_FORMAT(REPLACE(`json`->>'$[0].start_time',"12:00 AM","00:00 AM"), "%T")+12),24),":00 AM") 
                  AS TIME) 
           ELSE
                CAST((REPLACE(`json`->>'$[0].start_time',"12:00 AM","00:00 AM")) AS TIME)
            END    
   AND 
      TIME(ADDTIME(NOW(),"8:00:00")) <=
           CASE WHEN INSTR(REPLACE(`json`->>'$[0].end_time',"12:00 AM","00:00 AM"),"PM")>0 
           THEN 
                CAST(CONCAT(MOD((TIME_FORMAT(REPLACE(`json`->>'$[0].end_time',"12:00 AM","00:00 AM"), "%T")+12),24),":00 AM") 
                  AS TIME) 
           ELSE
                CAST(REPLACE(`json`->>'$[0].end_time',"12:00 AM","00:00 AM") AS TIME)                  
            END)

演示

赞(0）回复(0）举报 2021-07-24

u91tlkcl2#

也许是这样的：

SELECT *,CURRENT_TIME 
  FROM
(SELECT *,JSON_UNQUOTE(JSON_EXTRACT(`json`,'$[0].start_time')) AS st,
       JSON_UNQUOTE(JSON_EXTRACT(`json`,'$[0].end_time')) AS et FROM week2) V 
WHERE (JSON_SEARCH(`json`, 'one', DAYNAME(CURDATE()))  IS NOT NULL
       OR
     CURRENT_TIME hour >= 
      CASE WHEN st LIKE '%AM%' THEN REPLACE(st,' AM',':00') 
            WHEN st LIKE '%PM%' THEN SEC_TO_TIME(TIME_TO_SEC(REPLACE(st,' PM',':00'))+43200)
            END
      AND
      CURRENT_TIME hour <=
      CASE WHEN et LIKE '%AM%' THEN REPLACE(et,' AM',':00') 
            WHEN et LIKE '%PM%' THEN SEC_TO_TIME(TIME_TO_SEC(REPLACE(et,' PM',':00'))+43200)
            END);

第一个查询使用json_extract提取时间，使用json_unquote删除( " )然后将其作为子查询。
在外部查询中，找到 weekdays 使用json搜索的值与今天的值进行比较 DAYNAME(CURDATE()) ; 如果 JSON_SEARCH 如果没有找到它，它会回来的 NULL 因此 IS NOT NULL 使用条件。
追加 OR 比较 CURRENT_TIME 将时间值从 json 现场。但首先，使用 CASE 表达式来确定它是否 AM 或者 PM ; 如果是的话 AM ，只是 REPLACE 这个 AM 用秒针( :00 ). 如果是的话 PM ，执行 REPLACE 就像 AM 然后使用将时间值转换为秒 TIME_TO_SEC 然后加上43200秒（12小时），再次转换为标准 hour:minute:second 格式使用 SEC_TO_TIME .
p/s:这是我试图回答的问题，但我没有发帖，因为我在时间上得到了错误的结果。。只有当我把 CURRENT_TIME 在小提琴中我意识到小提琴的时区和我的不同。另外，我还在本地数据库上进行测试；这是mariadb和使用 STR_TO_TIME 是不同的。老实说，在我看来，如果这是mariadb，我认为有可能查询要短得多。

赞(0）回复(0）举报 2021-07-24

我来回答

mysql嵌套json列搜索并与今天进行比较

预期结果：

2条答案

相关问题

热门标签

最新问答