mysql - 范围之间的MySql JSON查询
问题描述
我有一张桌子card_percentage
,带有 DDL:
CREATE TABLE card_percentage
(
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
jdoc json NULL
);
它有一行数据
id | jdoc
------------
1 | {"AMXC": [{"enabled": "y", "bank_code": "AMXC", "max_amount": 99999999.99, "min_amount": 0.0, "percentage": 2.5}], "VISA": [{"enabled": "y", "bank_code": "VISA", "max_amount": 1999.99, "min_amount": 0.0, "percentage": 2.8}, {"enabled": "y", "bank_code": "VISA", "max_amount": 99999999.99, "min_amount": 2000.0, "percentage": 1.8}]}
美化 JSON 以提高可读性
{
"AMXC": [
{
"enabled": "y",
"bank_code": "AMXC",
"min_amount": 0.0,
"max_amount": 99999999.99,
"percentage": 2.5
}
],
"VISA": [
{
"enabled": "y",
"bank_code": "VISA",
"min_amount": 0.0,
"max_amount": 1999.99,
"percentage": 2.8
},
{
"enabled": "y",
"bank_code": "VISA",
"min_amount": 2000.0,
"max_amount": 99999999.99,
"percentage": 1.8
}
]
}
现在我想选择percentage
金额VISA
在和200
之间的时间。为此,我正在使用以下查询,但它在结果集中没有给出任何内容。min_amount
max_amount
SELECT id,
jdoc -> '$.VISA[*].percentage' percentage
FROM card_percentage
WHERE id = 1
AND jdoc -> '$.VISA[*].min_amount' <= 200
AND jdoc -> '$.VISA[*].max_amount' >= 200
;
解决方案
好的,基于@Akina 关于使用 JSON_TABLE 的评论,我做了进一步的研究。
以下是我的查询,效果很好。
SELECT id, jdoc.percentage
FROM card_percentage, JSON_TABLE(
jdoc,
'$.VISA[*]'
COLUMNS (
rowid FOR ORDINALITY,
enabled VARCHAR(1) PATH '$.enabled' DEFAULT '"n"' ON EMPTY DEFAULT '"n"' ON ERROR,
bank_code VARCHAR(4) PATH '$.bank_code' DEFAULT '"NCOD"' ON EMPTY ,
min_amount INT PATH '$.min_amount' DEFAULT '0' ON EMPTY ,
max_amount INT PATH '$.max_amount' DEFAULT '0' ON EMPTY ,
percentage DECIMAL(10, 2) PATH '$.percentage' DEFAULT '0' ON EMPTY DEFAULT '0' ON ERROR
)
) AS jdoc
WHERE jdoc.min_amount <= 200
AND jdoc.max_amount >= 200
;
为了让这项工作发挥作用,这篇文章“JSON_TABLE – 两全其美”确实很有帮助。
推荐阅读
- javascript - JS:按键访问嵌套数组
- amazon-ec2 - Amazon API 如何从 LB 获取 IP?
- windows - 在 WSL2 不工作的情况下在您的网络上创建反应应用程序
- wso2 - WSO2 Analytics 4.4.0:访问问题:/analytics-dashboard。原因:未找到
- wordpress - Wordpress:来自主题约会的虚拟数据不起作用
- php - 这个 php 用于将一种长度转换为另一种长度,但它没有给出输出
- azure-web-app-service - 从 Azure 应用服务访问时序见解 Gen2
- python - 基于熊猫条件的值总和
- r - 将字符串拆分为话语并将同一说话者的话语分配给数据框中的列
- flutter - Flutter中有什么方法可以进行webview缓存优化吗?