MySQL/MariaDB JSON_EXTRACT and JSON_CONTAINS

Question

I have a table with the following structure:

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            |                                                                                                                                                                                                            |

What I need is to do some searcches based on the listener_descriptions column. To ensure JSON_* method works, I did this query, that works fine:

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            |                                                                      |

Now I want to select all rows with matching SSLCertificateId:

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

But no results found. I have tried with multiple combinations of single and double quotes in the second parameter of JSON_CONTAINS without success.

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

Out of curiosity, why did you use JSON to store this data? The JSON data has the same set of attributes on every row. You could have just created real columns for each of the attributes. Then you could search with `WHERE SSLCertificateId = '...'` — Bill Karwin, Aug 29 '18 at 18:22
@BillKarwin `listener_descriptions` is a list, it can contain multiple objects, so `SSLCertificateId` can be multiple. There are a lot of approaches, most of them better, but... — okelet, Aug 29 '18 at 18:28
That sounds like a simple one-to-many relationship. You should have a `load_balancer` table and a `listener` table with one listener per row, each referencing its `load_balancer`. If you have multiple policies per listener, then a third table `listener_policy` with one policy per row, each referencing its listener. — Bill Karwin, Aug 29 '18 at 19:36

score 7 · Accepted Answer · answered Aug 29 '18 at 18:18

JSON_CONTAINS() doesn't allow [*] in its path. Instead, use JSON_EXTRACT() to extract the array of all certs, and use JSON_CONTAINS() on that.

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

DEMO

MySQL/MariaDB JSON_EXTRACT and JSON_CONTAINS

1 Answers1