MySql Query with Json

Question

I came across a couple of days that I can't solve. I know the basic MySql but I was trying to work with SELECTs to recover data in JSON.

I have a table in the database called home_slider.

Inside I have the following columns:

id_shop, id_lang, id_image and json_groups_id.

They are all INT fields except json_groups_id where I insert files via json_econde.

The JSON in the "json_groups_id" field is structured like this:

{"2": "Guest", "3": "Customer", "4": "Custom"}

All these data are saved in a simple form.

In the end I find myself different are similar to this:

+------------+----------------+-----------------+-------------------------------+
|   shop_id  |    id_lang     |   id_image | json_groups_id                     |
+------------+----------------+-----------------+--------------------------------+
|       1    |        1       |      2     | {"2": "Guest", "3": "Customer"}     |
|       1    |        1       |      3     | {"1": "Visit", "4": "Other"}        |
|       1    |        1       |      4     | {"2": "Guest", "5": "Test"}
+-------------+----------------+-----------------+--------------------------------+

The problem arrives now, given an array similar to this [2,5,4] . I would like to retrieve all the rows that in the "json_groups_id" field have that key. I tried whith JSON_EXTRACT but I couldn't do it. What kind of select can I use? Thanks Mario

Just a quick check... What version of MySQL are you using please — RiggsFolly, Mar 18 '19 at 14:32
TBH I'm not very happy with the approach of filtering data directly over a column that contains a "raw data" / object field like JSON. Just as a naive idea, wouldn't it make more sense to depict this kind of association in a separate table so you can query it simply by ID? — thex, Mar 18 '19 at 14:44
I have yet to see any question on Stack Overflow about storing JSON in MySQL, where using JSON was a good idea. It's better to store data in a normalized manner. — Bill Karwin, Mar 18 '19 at 15:35
With MySQL 5.6 you cannot use JSON functions. Have a look at my answer here: https://stackoverflow.com/questions/37816269/how-to-get-values-from-mysql5-6-column-if-that-contains-json-document-as-strin/52402431#52402431 -- these functions may be useful for you, too. — gaborsch, Mar 18 '19 at 16:04

score 2 · Answer 1 · answered Mar 18 '19 at 14:44

You should normalize your database, having it at least the 3rd normal form. So the atomic data would lie in a separate field. Then you can easly select the data, using there WHERE condition, but as a crutch, you can use LIKE for current structure.

SELECT * FROM `table` WHERE `json_groups_id` LIKE '{"YOURNUMBER1"%' OR `json_groups_id` LIKE '{"YOURNUMBER2"%' OR `json_groups_id` LIKE '{"YOURNUMBER3"%'

Also, you can try JSON_CONTAINS() https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

But this is not a good practice, imho

I also think this would be the way to go. Separate the data into another table so that you can easily query it without the need of applying quite slow filters on a column. 1:N relationship. Also this would make it easier to extend in the future. — thex, Mar 18 '19 at 14:54

thex · Accepted Answer · 2019-03-18T16:12:50.687

Based on the information you provided I assume you need M:N relationship to depict your relationship between the JSON data and the original table you outlined in your post.

Your original Table (updated)

+------------+----------------+------------+
|   shop_id  |    id_lang     |   id_image |
+------------+----------------+------------+
|       1    |        1       |      2     |
|       1    |        1       |      3     |
|       1    |        1       |      4     | 
+------------+----------------+------------+

New Table

+-------------+----------------+
|   group_id  |    desc/name   |
+-------------+----------------+
|       1     |    Visit       |
|       2     |    Guest       |
|       3     |    Customer    |
|       4     |    Other       |
|       5     |    Test        |
+-------------+----------------+

Relationship/Join Table:

+-------------+-------------------+
| FK group_id | FK Original Table |
+-------------+-------------------+
|       1     |          A        |
|       1     |          B        |
|       2     |          C        |
|       4     |          D        |
|       5     |          E        |
+-------------+-------------------+

Obviously you have in this case to either store the JSON as an duplicate or what would be even better and this is also what I would recommend => reconstruct it from the data you fetch from the DB.

The above table example is just as a hint how this could be realised on DB level. If you don't need a reconstruction of the data I wouldn't even bother at all on the original JSON. I don't know what the primary key of you original table is so I added a generic key for the join table.

Updated: removed 1:N because M:N is needed.

ok so I seemed to understand that the approach is wrong. I try to create two tables and relate them to each other. Basically what I want to get are all the id_image that belong to a single or multiple id_groups that I have in an array. Now I try to relate the tables let's see what happens — Mario gasparella, Mar 18 '19 at 15:35
@Mariogasparella If the same group id relates to different rows you need M:N and the second approach. However I will now update my answer as you need M:N from what I understand. Because you have the same group id in multiple rows. — thex, Mar 18 '19 at 16:09
@Mariogasparella realising this without JSON in a column is far easier and more sophisticated. I know it might be more work in the first place but this is the way to go. Also it is far easier to extend if you need more information added to your group. — thex, Mar 18 '19 at 16:18
thanks guys, I had to take into account also the directives and business decisions .. at the time I used JSON_EXTRACT but I'm rewriting the code to create a relational table. Thanks a lot for the advice — Mario gasparella, Mar 20 '19 at 08:42

score 0 · Answer 3 · answered Jun 23 '20 at 08:39

You can definitely to it using JSON_EXTRACT() function in mysql.It migth be that you are doing some or another kind of mistake . Let me explain it using an example:

lets take a table that contains JSON (table client_services here) :

+-----+-----------+--------------------------------------+
| id  | client_id | service_values                       |
+-----+-----------+------------+-------------------------+
| 100 |      1000 | { "quota": 1,"data_transfer":160000} |
| 101 |      1000 | { "quota": 2,"data_transfer":800000} |
| 102 |      1000 | { "quota": 3,"data_transfer":70000}  |
| 103 |      1001 | { "quota": 1,"data_transfer":97000}  |
| 104 |      1001 | { "quota": 2,"data_transfer":1760}   |
| 105 |      1002 | { "quota": 2,"data_transfer":1060}   |
+-----+-----------+--------------------------------------+

To Select each JSON fields , run this query :

SELECT 
    id, client_id, 
    json_extract(service_values, '$.quota') AS quota,
    json_extract(service_values, '$.data_transfer') AS data_transfer
FROM client_services;

So the output will be :

+-----+-----------+----------------------+
| id  | client_id | quota | data_transfer|
+-----+-----------+----------------------+
| 100 |      1000 |     1 |       160000 |
| 101 |      1000 |     2 |       800000 |
| 102 |      1000 |     3 |        70000 |
| 103 |      1001 |     1 |        97000 |
| 104 |      1001 |     2 |         1760 |
| 105 |      1002 |     2 |         1060 |
+-----+-----------+----------------------+

I know that you tried it using json_extract() , but it might be so that you made some minute mistake. Examine this example carefully , I am sure that it will work.

MySql Query with Json

3 Answers3