MYSQL: How to query where JSON array contain specific label

Question

MySQL 5.7.24

Lets say I have 3 rows like this:

ID (PK) | Name (VARCHAR) | Data (JSON)
--------+----------------+-------------------------------------
 1      | Admad          | [{"label":"Color", "value":"Red"}, {"label":"Age", "value":40}]
 2      | Saleem         | [{"label":"Color", "value":"Green"}, {"label":"Age", "value":37}, {"label":"Hoby", "value":"Chess"}]
 3      | Daniel         | [{"label":"Food", "value":"Grape"}, {"label":"Age", "value":47}, {"label":"State", "value":"Sel"}]

Rule #1: The JSON column is dynamic. Means not everybody will have the same structure

Rule #2: Assuming I can't modify the data structure

My question, it it possible to query so that I can get the ID of records where the Age is >= 40? In this case 1 & 3.

Additional Info (after being pointed as duplicate): if you look at my data, the parent container is array. If I store my data like

{"Age":"40", "Color":"Red"} 

then I can simply use

Data->>'$.Age' >= 40

My current thinking is to use a stored procedure to loop the array but I hope I don't have to take that route. The second option is to use regex (which I also hope not). If you think "JSON search" is the solution, kindly point to me which one (or some sample for this noob of me). The documentation's too general for my specific needs.

Answer 1

Here's a demo:

mysql> create table letsayi (id int primary key, name varchar(255), data json);

mysql> > insert into letsayi values
-> (1, 'Admad', '[{"label":"Color", "value":"Red"}, {"label":"Age", "value":"40"}]'),
-> (2, 'Saleem', '[{"label":"Color", "value":"Green"}, {"label":"Age", "value":"37"}, {"label":"Hoby", "value":"Chess"}]');

mysql>  select id, name from letsayi 
        where json_contains(data, '{"label":"Age","value":"40"}');
+----+-------+
| id | name  |
+----+-------+
|  1 | Admad |
+----+-------+

I have to say this is the least efficient way you could store your data. There's no way to use an index to search for your data, even if you use indexes on generated columns. You're not even storing the integer "40" as an integer — you're storing the numbers as strings, which makes them take more space.

Using JSON in MySQL when you don't need to is a bad idea.

---

Is it still possible to query age >= 40?

Not using JSON_CONTAINS(). That function is not like an inequality condition in a WHERE clause. It only matches exact equality of a subdocument.

To do an inequality, you'd have to upgrade to MySQL 8.0 and use JSON_TABLE(). I answered another question recently about that: MySQL nested JSON column search and extract sub JSON

In other words, you have to convert your JSON into a format as if you had stored it in traditional rows and columns. But you have to do this every time you query your data.

If you need to use conditions in the WHERE clause, you're better off not using JSON. It just makes your queries much too complex. Listen to this old advice about programming:

"Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it." — Brian Kernighan

---

how people tackle dynamically added form fields

You could create a key/value table for the dynamic form fields:

CREATE TABLE keyvalue (
  user_id INT NOT NULL,
  label VARCHAR(64) NOT NULL,
  value VARCHAR(255) NOT NULL,
  PRIMARY KEY (user_id, label),
  INDEX (label)
);

Then you can add key/value pairs for each user's dynamic form entries:

INSERT INTO keyvalue (user_id, label, value)
VALUES (123, 'Color', 'Red'),
       (123, 'Age', '40');

This is still a bit inefficient in storage compared to real columns, because the label names are stored every time you enter a user's data, and you still store integers as strings. But if the users are really allowed to store any labels of their own choosing, you can't make those real columns.

With the key/value table, querying for age > 40 is simpler:

SELECT user_id FROM key_value
WHERE label = 'Age' AND value >= 40