时间戳

首页 > 解决方案 > MySQL 嵌套 JSON 列搜索并与今天进行比较

mysql - MySQL 嵌套 JSON 列搜索并与今天进行比较

问题描述

我正在使用 MySQL 版本 5.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"}]');

标签： mysqlsqljsonmysql-5.7json-extract

解决方案

您需要检测日期名称(1)和时间段(2) ，并在最后通过OR运算符组合这两个条件。

For (1)：使用函数检测当天的名称，DAYNAME()并通过函数查找包含工作日键的JSON数据中是否存在JSON_CONTAINS。

For (2) :数据类型的TIME()函数和CASTing 字符串TIME可能与一个技巧一起使用，为PM类型时间的情况添加 12 小时的迭代。

所以，考虑使用：

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)

Demo

推荐阅读