代码之家 › 专栏 › 技术社区 › Sasha

mysql-搜索json类型的行以查找打开的存储

mysql

Sasha · 技术社区 · 6 年前

我有一张桌子,把工作时间储存在 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"
}

目前只有第一个 -至是活动的, -至2 仍然不相关。

当前MySQL版本:

mysql  Ver 14.14 Distrib 5.7.22

1 回复 | 直到 6 年前

gabe3886 6 年前

这个 MySQL manual 有函数的示例 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
    )

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

1 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

1 年前

hello_programmers · Mysql从其他表输出一列

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前

IJustSentYouAPackage · MySQL错误:引用的表中缺少约束的列[已关闭]

1 年前

Popo · Sql查询:返回数据库中不可用的where条件

1 年前

Roberts · DATE_FORMAT和STR_TO_DATE不工作

1 年前

Hamdan Nuramdani · 对账单中一周内不同表中的数据求和

1 年前

Kugelfisch · 用php为数据库加密数据

1 年前