时间戳

首页 > 解决方案 > MySQL - 搜索 JSON 类型行以查找打开的商店

mysql - MySQL - 搜索 JSON 类型行以查找打开的商店

问题描述

我有一张以JSON格式存储工作时间的表格。我试图找到方法来选择查询时打开的所有商店。

这是存储小时数的示例（存储在数据库中，未格式化）：

{"friday-to": "18:00", "monday-to": "18:00", "sunday-to": null, "friday-to2": "06:00", "monday-to2": "06:00", "sunday-to2": null, "tuesday-to": "18:00", "friday-from": "07:00", "monday-from": "07:00", "saturday-to": "15:00", "sunday-from": null, "thursday-to": "18:00", "tuesday-to2": "06:00", "friday-from2": "06:00", "monday-from2": "06:00", "saturday-to2": "06:00", "sunday-from2": null, "thursday-to2": "06:00", "tuesday-from": "07:00", "wednesday-to": "18:00", "saturday-from": "09:00", "thursday-from": "07:00", "tuesday-from2": "06:00", "wednesday-to2": "06:00", "saturday-from2": "06:00", "thursday-from2": "06:00", "wednesday-from": "07:00", "wednesday-from2": "06:00"}

形成更好的视野：

{
    "friday-to": "18:00",
    "monday-to": "18:00",
    "sunday-to": null, 
    "friday-to2": "06:00", 
    "monday-to2": "06:00",
    "sunday-to2": null, 
    "tuesday-to": "18:00", 
    "friday-from": "07:00", 
    "monday-from": "07:00", 
    "saturday-to": "15:00", 
    "sunday-from": null, 
    "thursday-to": "18:00", 
    "tuesday-to2": "06:00", 
    "friday-from2": "06:00", 
    "monday-from2": "06:00", 
    "saturday-to2": "06:00", 
    "sunday-from2": null, 
    "thursday-to2": "06:00", 
    "tuesday-from": "07:00", 
    "wednesday-to": "18:00", 
    "saturday-from": "09:00", 
    "thursday-from": "07:00", 
    "tuesday-from2": "06:00", 
    "wednesday-to2": "06:00", 
    "saturday-from2": "06:00", 
    "thursday-from2": "06:00", 
    "wednesday-from": "07:00", 
    "wednesday-from2": "06:00"
}

目前只有第一个-to处于活动状态，-to2仍然不相关。

例如，我想查找今天（当前是星期二）开张的所有商店。是否有可能做到这一点？

当前 MySQL 版本：

mysql  Ver 14.14 Distrib 5.7.22

标签： mysql

解决方案

MySQL 手册JSON_EXTRACT提供了可能有用的函数示例。它表明您可以按照以下方式进行操作：

SELECT c, JSON_EXTRACT(c, "$.id"), g
FROM jemp
WHERE JSON_EXTRACT(c, "$.id") > 1
ORDER BY JSON_EXTRACT(c, "$.name");

所以你可以这样做：

SELECT * FROM my_table 
WHERE (
    JSON_EXTRACT(field, "$.tuesday-from") IS NOT NULL
    OR JSON_EXTRACT(field, "$.tuesday-from2") IS NOT NULL
    )

推荐阅读