How to perform add, update and delete on this complex json data type field in MySQL?

Question

I am totally new to JSON data type in MySQL. I installed MySQL 5.7.34 a few days ago and now I'm playing around with it.

So, I have following table in MySQL 5.7.34:

CREATE TABLE tb_products (
    product_id int(11) NOT NULL AUTO_INCREMENT, 
    product_name varchar(255) NOT NULL, 
    product_attributes json NOT NULL,
    PRIMARY KEY (product_id)
);

One of the row in this table looks like as:

product_attributes field:

[
    {
        "site": "site1",
        "categories": [
            "site1 cat1",
            "site1 cat2",
            "site1 cat3"
        ]
    },
    {
        "site": "site2",
        "categories": [
            "site2 cat1",
            "site2 cat2"
        ]
    }
]

My first question is that have I put above JSON data in proper format for doing various further operations?

Or whether it should have a key attributes for easy accessing the data:

{
    "attributes": [
        {
            "site": "site1",
            "categories": [
                "site1 cat1",
                "site1 cat2",
                "site1 cat3"
            ]
        },
        {
            "site": "site2",
            "categories": [
                "site2 cat1",
                "site2 cat2"
            ]
        }
    ]
}

Now, I need to perform below operations but I can't figure out how to do all this:

How to select row where site = site1?
How to select row where site = site1 and categories = site1 cat1?
How to add one another object {"site": "site3", "categories": [...] to the product_attributes column?
How to add one another array element site1 cat4 where site = site1?
How to remove attribute site = site1?
How to remove array value from categories site1 cat1 where site = site1?

I have already read this tutorial. Its very short and incomplete. Doesn't solve my purpose. — Sachin, Jul 06 '21 at 16:55
https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql — rahularyansharma, Jul 06 '21 at 16:56
https://stackoverflow.com/questions/49053341/mysql-select-where-json-field-property-has-value — rahularyansharma, Jul 06 '21 at 16:56
One problem/question per (question) post please not six or even seven. Open multiple posts, if you have multiple problems. — sticky bit, Jul 06 '21 at 17:01
@stickybit ok, I understand. But... so how often would I have to type and post almost the same type of content which would be tedious and time consuming. — Sachin, Jul 06 '21 at 17:03

Bill Karwin · Answer 1 · 2021-07-06T18:07:06.820

Given what you've shown, there's no need to make this JSON into an object with an attributes key. I don't see any other key in use, and you have not mentioned any expectation of adding a second key.

How to select row where site = site1?

select product_id from tb_products 
where json_contains(product_attributes, '{"site":"site1"}');

How to select row where site = site1 and categories = site1 cat1?

select product_id from tb_products
where json_contains(product_attributes, '{"site":"site1"}')
 and json_search(product_attributes, 'one', 'site1 cat2') LIKE '%.categories[%';

How to add one another object {"site": "site3", "categories": [...] to the product_attributes column?

update tb_products 
set product_attributes = json_array_append(product_attributes, '$', 
  cast('{"site": "site3", "categories":["..."]}' as json));

How to add one another array element "site1 cat4" where site = site1?

This is getting into finding elements by both key and value. The better solution to doing this in JSON is the JSON_TABLE() function in MySQL 8.0. There isn't a good way to do it in MySQL 5.7.

You end up doing JSON_SEARCH() which only searches by value, and then you have to do substring matching on the path it returns. This is difficult for you to develop, difficult to maintain the code, and impossible to optimize.
How to remove attribute site = site1?

Same as previous.
How to remove array value from categories site1 cat1 where site = site1?

Same as previous.

Ultimately, if you try to use JSON like rows in a normal SQL table, it will be awkward and inefficient. Gradually, MySQL is adding more JSON functions to do the necessary operations, but it's not as elegant as doing the same thing in SQL.

Another issue: searching JSON data in your WHERE clause can't use an index. You will end up doing expensive table-scans a lot.

Yet another issue: storing data in JSON takes 2x to 3x as much space as storing the same data in normal rows and columns. As your database grows larger, you will quickly run out of space.

Thanks Bill. I'm glad to see that you took interest in giving answers to my questions. — Sachin, Jul 06 '21 at 17:56
You might like my presentation [How to Use JSON in MySQL Wrong](https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong). — Bill Karwin, Jul 06 '21 at 18:07
Query 2: SELECT * FROM `tb_products` WHERE JSON_CONTAINS( product_attributes, '{"site":"site1"}' ) AND JSON_CONTAINS( JSON_EXTRACT( JSON_EXTRACT( `product_attributes`, '$[*].categories' ), '$[0]' ), '"site1 cat1"' ) — Indra Kumar S, Jul 06 '21 at 18:08
@IndraKumarS Thanks, you might like to post an answer yourself. But it supports the point I'm trying to make that using JSON is _harder_ than using normal rows and columns. — Bill Karwin, Jul 06 '21 at 18:09
@BillKarwin so there is no straightforward solution to my last 3 questions in MySQL 5.7.34? Btw, I went through your presentation. it's quite informative. — Sachin, Jul 06 '21 at 18:36
@BillKarwin can't we use `JSON_REMOVE()` to remove some key:value pair? And `JSON_APPEND()` won't work for adding new array element? — Sachin, Jul 06 '21 at 18:43

How to perform add, update and delete on this complex json data type field in MySQL?

1 Answers1