CODEWITHSUNDEEP
phpmysqljson
Mario gasparella
Mario gasparella

Reputation: 85

MySql Query with Json

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

Upvotes: 1

Views: 198

Answers (3)

vishwampandya
vishwampandya

Reputation: 1167

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.

Upvotes: 0

thex
thex

Reputation: 620

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.

Upvotes: 0

Sergej
Sergej

Reputation: 2196

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

Upvotes: 2

Related Questions