Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    I think UsingJSON_CONTAINS can make your query more explicit and potentially faster by avoiding string-based operations like LIKE.

       SELECT id, jsonval
    FROM sometable
    WHERE JSON_CONTAINS(jsonval->'$.programs[*].id', '14');
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search