Querying in multilevel JSON in MariaDB/MySQL for specific value

Question

I'm working with a MariaDB table that has a JSON value stored as follows:

{"nextValue":4,"1":{"text":"Item1","textDisplay":"","value":1,"isActive":0},"2":{"text":"Item2","textDisplay":"","value":2,"isActive":1},"3":{"text":"Item3","textDisplay":"","value":3,"isActive":1}}

What I'm trying to do is build a query where I can have a column that as the item, ie "Item2" and in the next column have the value for the key "value" from that JSON. So, if "Item2" returns, in the column next to it, it would have "2".

I've tried many of the JSON options available for MariaDB, but I just can't figure out how to do this.

Out of curiosity, why are you storing this as JSON? Your queries would be much easier if you CREATE TABLE Items (id INT AUTO_INCREMENT PRIMARY KEY, text VARCHAR(20), textDisplay TEXT, value INT, isActive BOOL) and store three rows in it. — Bill Karwin
The developers at my company made that call. Unfortunately, I'm stuck with pulling it out for the custom reports I'm creating for the client. — Eric Shreve

Bill Karwin Bill Karwin · Accepted Answer · 2019-03-25T23:55:32

I tested the following with MySQL 8.0. It uses functions that seem to be present in MariaDB according to its documentation, but I don't have a MariaDB instance to test.

SELECT * FROM mytable;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, "2": {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, "3": {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}, "nextValue": 4} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(data, REPLACE(JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'Item2')), '.text', '')), '$.value')) AS value FROM mytable;
+-------+
| value |
+-------+
| 2     |
+-------+

This is incredibly fragile, relying on doing string-replacement on JSON paths and then using those paths in further JSON functions. It's going to cost your employer a lot of engineer-hours to develop and maintain such complex SQL statements.

An alternative would be to upgrade to MySQL 8.0 and use the JSON_TABLE() function to map your JSON document into a derived table, then you could use conditions in a WHERE clause.

SELECT j.* FROM mytable2, 
JSON_TABLE(mytable2.data, '$[*]' 
  COLUMNS (
    rowid FOR ORDINALITY,
    `text` VARCHAR(20) PATH '$.text',
    textDisplay TEXT PATH '$.textDisplay',
    value INT PATH '$.value',
    isActive BOOL PATH '$.isActive'
  )
) AS j

+-------+-------+-------------+-------+----------+
| rowid | text  | textDisplay | value | isActive |
+-------+-------+-------------+-------+----------+
|     1 | Item1 |             |     1 |        0 |
|     2 | Item2 |             |     2 |        1 |
|     3 | Item3 |             |     3 |        1 |
+-------+-------+-------------+-------+----------+

But that won't work with the JSON data you have, because the function only works if your JSON is an array, whereas your data is formatted as a JSON object. I made the example above work only when I changed your JSON data format to an array:

select * from mytable2;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The JSON format designed by your developers is designed to make it easy for them to insert data, but it's a totally gratuitous use of JSON, and it makes queries against the data unnecessarily complex. It's an example of the Inner-Platform Effect:

The inner-platform effect is the tendency of software architects to create a system so customizable as to become a replica, and often a poor replica, of the software development platform they are using. This is generally inefficient and such systems are often considered to be examples of an anti-pattern.

As I commented above, I would design this as a normal SQL table:

CREATE TABLE Items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `text` VARCHAR(20), 
  textDisplay TEXT, 
  value INT, 
  isActive BOOL
);

The populate it with values in each column:

INSERT INTO Items 
VALUES (1, 'Item1', '', 1, 0),
       (2, 'Item2', '', 2, 1),
       (3, 'Item3', '', 3, 1);

Then you can query it very simply:

SELECT value FROM Items WHERE `text` = 'Item2';
+-------+
| value |
+-------+
|     2 |
+-------+

Your employer should favor the simplicity of storing data in a normal manner, because they will save lots of time and money when it comes time to query the data.

Querying in multilevel JSON in MariaDB/MySQL for specific value

1 Answers