CODEWITHSUNDEEP
mysqljsonjson-extract
Overbeeke
Overbeeke

Reputation: 2028

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?

Upvotes: 2

Views: 517

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562861

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.

Upvotes: 3

TSCAmerica.com
TSCAmerica.com

Reputation: 5417

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');

Upvotes: 1

Related Questions