json_contains_extract, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

drop table if exists test_table;

create table test_table (
    id int primary key auto_increment,
    load_balancer_name varchar(20),
    listener_descriptions JSON null
);
    
INSERT INTO test_table (load_balancer_name, listener_descriptions) VALUES
('with_cert_1', '[{"Listener": {"Protocol": "HTTPS", "LoadBalancerPort": 443, "InstanceProtocol": "HTTP", "InstancePort": 9005, "SSLCertificateId": "arn:aws:acm:us-west-2:xxxx:certificate/xxx"}, "PolicyNames": ["xxxx"]}]'),
('with_cert_1', '[{"Listener": {"Protocol": "HTTPS", "LoadBalancerPort": 443, "InstanceProtocol": "HTTP", "InstancePort": 9005, "SSLCertificateId": "arn:aws:acm:us-west-2:xxxx:certificate/xxx"}, "PolicyNames": ["xxxx"]}]'),
('with_cert_2', '[{"Listener": {"Protocol": "HTTPS", "LoadBalancerPort": 443, "InstanceProtocol": "HTTP", "InstancePort": 9005, "SSLCertificateId": "arn:aws:acm:us-west-2:xxxx:certificate/yyy"}, "PolicyNames": ["xxxx"]}]'),
('no_cert', null);

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


absolute service time: 0,37 sec