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
– Bill Karwin, Commented Aug 29, 2018 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
– okelet, Commented Aug 29, 2018 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
– Bill Karwin, Commented Aug 29, 2018 at 19:36

Barmar · Accepted Answer · 2018-08-29 18:18:44Z

7

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

answered Aug 29, 2018 at 18:18

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL/MariaDB JSON_EXTRACT and JSON_CONTAINS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related