CODEWITHSUNDEEP
mysqlsqljson
mayank agrawal
mayank agrawal

Reputation: 2545

Find all json keys if their value meets a condition in mysql

I have a json column in table which contains key values pairs in each row as,

{"100" : 1, "102" : 3, "103" : 3}

I want to query the column to get all the JSON_KEYS which have value = 3. The keys will be unknown so I am not able to use JSON_EXTRACT. My output should be,

JSON_ARRAY("102", "103")

I am using AURORA MYSQL 5.7 and hence cannot use JSON_TABLE

Upvotes: 1

Views: 1968

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

What you are describing, searching JSON values and returning the keys, is what JSON_SEARCH() does. If you're working with JSON data in MySQL, you should just read all the documentation for the built-in JSON functions. There's not that many, and it will save you a lot of time if you become familiar with the choices.

However, there's a caveat: JSON_SEARCH() doesn't work for searching integers or booleans. It can only search for string values.

Here's a demo:

mysql> select json_search(cast('{"100" : 1, "102" : 3, "103" : 3}' as json), 'all', 3) as `keys`;
+------+
| keys |
+------+
| NULL |
+------+

No matches found.

But if I were to change the 3 values to strings, it works:

mysql> select json_search(cast('{"100" : 1, "102" : "3", "103" : "3"}' as json), 'all', '3') as `keys`;
+----------------------------+
| keys                       |
+----------------------------+
| ["$.\"102\"", "$.\"103\""] |
+----------------------------+

Given that your JSON data stores integer values, the only solutions I can think of are:

  • Fetch all the data into your client application, deserialize the JSON documents into hash arrays, and search them one by one.

  • Create another table that is an inverted index:

    CREATE TABLE jsonkeys_by_value (
  value int not null,
  id int not null, -- references the primary key of your table
  json_key int not null, -- references a key in the JSON document
  PRIMARY KEY (value, id, json_key) 
);

    Then search all your JSON data manually one time, filling this table with back-references to every occurrence of every value found. Then you can search it:

    SELECT ...
FROM jsonkeys_by_value AS v
JOIN your_table_with_json AS t USING (id) 
WHERE value = 3;

    But this still doesn't tell you which keys have the value 3, only that there are one or more keys in the JSON document with the value 3. At least it narrows down the search, but you'll still have to inspect the JSON document using application code.

You also cannot use a generated column, since you said the JSON keys are variable.

You could use a regular expression to find out if the JSON contains the value 3, but that won't tell you the corresponding keys. There's no REGEXP_REPLACE() function until MySQL 8.0, and so far there's no Amazon Aurora version based on MySQL 8.0.

One more caveat: Amazon Aurora does not have a fully compatible implementation of JSON as MySQL 5.7. They forked the MySQL 5.7 code at some point, so they are lacking some bug fixes and features. I know at least the functions JSON_ARRAYAGG() and JSON_OBJECTAGG() are missing, last time I checked. Those functions were implemented in MySQL in version 5.7.22, and Aurora doesn't have them. Aurora is not open-source, so we don't know without careful testing what other features it's missing.

Upvotes: 1

Related Questions