mysql - MySQL/MariaDB JSON_EXTRACT和JSON_CONTAINS

我有一个具有以下结构的表：

select * from test_table;

id |load_balancer_name |listener_descriptions                                                                                                                                                                                       |
---|-------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
1  |with_cert_1        |[{"Listener": {"Protocol": "HTTPS", "LoadBalancerPort": 443, "InstanceProtocol": "HTTP", "InstancePort": 9005, "SSLCertificateId": "arn:aws:acm:us-west-2:xxxx:certificate/xxx"}, "PolicyNames": ["xxxx"]}] |
2  |with_cert_1        |[{"Listener": {"Protocol": "HTTPS", "LoadBalancerPort": 443, "InstanceProtocol": "HTTP", "InstancePort": 9005, "SSLCertificateId": "arn:aws:acm:us-west-2:xxxx:certificate/xxx"}, "PolicyNames": ["xxxx"]}] |
3  |with_cert_2        |[{"Listener": {"Protocol": "HTTPS", "LoadBalancerPort": 443, "InstanceProtocol": "HTTP", "InstancePort": 9005, "SSLCertificateId": "arn:aws:acm:us-west-2:xxxx:certificate/yyy"}, "PolicyNames": ["xxxx"]}] |
4  |no_cert            |                                                                                                                                                                                                            |

我需要基于listener_descriptions列进行一些搜索。为确保JSON_*方法有效，我
执行此查询，效果很好：

select
id, load_balancer_name,
JSON_EXTRACT(listener_descriptions, "$[*].Listener.SSLCertificateId")
from test_table;

id |load_balancer_name |JSON_EXTRACT(listener_descriptions, "$[*].Listener.SSLCertificateId") |
---|-------------------|----------------------------------------------------------------------|
1  |with_cert_1        |["arn:aws:acm:us-west-2:xxxx:certificate/xxx"]                        |
2  |with_cert_1        |["arn:aws:acm:us-west-2:xxxx:certificate/xxx"]                        |
3  |with_cert_2        |["arn:aws:acm:us-west-2:xxxx:certificate/yyy"]                        |
4  |no_cert            |                                                                      |

现在，我要选择所有匹配SSLCertificateId的行：

select
*
from test_table
where JSON_CONTAINS(listener_descriptions, '"arn:aws:acm:us-west-2:xxxx:certificate/xxx"', "$[*].Listener.SSLCertificateId")
;

但未找到结果。我尝试在JSON_CONTAINS的第二个参数中使用单引号和双引号的多种组合，但均未成功。

version()                                |
-----------------------------------------|
10.3.8-MariaDB-1:10.3.8+maria~bionic-log |

最佳答案

JSON_CONTAINS()不允许在其路径中使用[*]。相反，使用JSON_EXTRACT()提取所有证书的数组，然后在其上使用JSON_CONTAINS()。

select *
FROM test_table
WHERE JSON_CONTAINS(JSON_EXTRACT(listener_descriptions, "$[*].Listener.SSLCertificateId"), '"arn:aws:acm:us-west-2:xxxx:certificate/xxx"')
;

DEMO

关于mysql - MySQL/MariaDB JSON_EXTRACT和JSON_CONTAINS，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52083608/