MySql searching in JSON object with array with objects on specific property value

I have a table 'sometable' with a JSON field named 'jsonval', which contains a JSON object with the property 'programs' that contains an array of objects with the property 'id'

I want to search for records that contain jsonval.programs.id = 14

The following query works:

SELECT id, jsonval, JSON_EXTRACT(jsonval, '$.programs[*].id') FROM `sometable` 
WHERE JSON_EXTRACT(jsonval, '$.programs[*].id') LIKE '%"14"%';

because JSON_EXTRACT(jsonval, '$.programs[*].id') results in the string representation of the array holding the id's, i.e.: ["14","26"]

But there should be a more elegant solution, maybe using JSON_CONTAINS?

Solution

Edit: I wrote this answer before the OP revealed that they are using MariaDB 10.6, not MySQL. I'll leave it here for the benefit of readers who are using MySQL.

This is the current solution. MySQL 8.0.17 or later support the MEMBER OF predicate.

SELECT * FROM sometable
WHERE 14 MEMBER OF (jsonval->'$.programs');

This is better than using JSON_CONTAINS() because you can create a multi-valued index on the array (also requires MySQL 8.0.17).

ALTER TABLE sometable ADD KEY ((CAST(jsonval->'$.programs' AS UNSIGNED ARRAY)));

EXPLAIN
SELECT * FROM sometable
WHERE 14 MEMBER OF (jsonval->'$.programs');

The EXPLAIN shows that it uses the index:

+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | sometable | NULL       | ref  | functional_index | functional_index | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+

In general I think anytime you need to reference a JSON column in your WHERE clause, you're better off storing data in normal rows and columns. Writing queries for JSON data results in more complex queries and some cases cannot be optimized.