JSON_REMOVE object in MySQL

Question

I am trying to delete objects from my JSON array in MySQL.

I have a table called cart with two fields quote_id type int and items type json with the following row stored inside MySQL

quote_id: 0

items:

[ { "a":42, "b":"test4" }, { "a":32, "b":"test3" } ]

I am trying to create a query which would delete json objects from the json array. For example every { "a":32, "b":"test3" }

I have tried many queries. First I ended up with this:

UPDATE cart
SET items = IFNULL(JSON_REMOVE(items, JSON_UNQUOTE(JSON_SEARCH(items, 'one', 'test3'))), items)
WHERE quote_id = 13392;

However it just deletes "b":"test3" from the second object and left the "a":32 in it and I need a query that would find the whole object and would delete it.

This is my second query:

UPDATE cart
SET items = IFNULL(JSON_REMOVE(items, JSON_SEARCH(items, 'one', CAST('{"a": 32, "b": "test3"}' AS JSON))), items)
WHERE quote_id = 13392;

However I don't think the search on it works. I tried it without using the CAST()AS JSON, however it still did not work.

As I said I am pretty sure the problem is with the JSON_SEARCH, but maybe someone has the solution?

Thank you!

What is your datatype of that field?

Pinal Patel
– Pinal Patel

2019-05-08 06:36:19 +00:00
Commented May 8, 2019 at 6:36 — Pinal Patel
– Pinal Patel, Commented May 8, 2019 at 6:36
Which one, info? It's JSON.

Marius
– Marius

2019-05-08 07:05:19 +00:00
Commented May 8, 2019 at 7:05 — Marius
– Marius, Commented May 8, 2019 at 7:05

Sebastian Brosch · Accepted Answer · 2019-05-08 10:43:40Z

1

The JSON_SEARCH returns the path of the property, not the path to the object itself.

So you can use the following solution to get the object path with SUBSTR:

SELECT JSON_REMOVE(items, 
    SUBSTR(JSON_UNQUOTE(JSON_SEARCH(items, 'one', 'test3')), 1, LOCATE('.', JSON_UNQUOTE(JSON_SEARCH(items, 'one', 'test3')))-1)
) FROM cart

You can also use REGEXP_SUBSTR to get the object path:

SELECT JSON_REMOVE(items, REGEXP_SUBSTR(JSON_UNQUOTE(JSON_SEARCH(items, 'one', 'test3')), '^\\$\\[[0-9]+\\]'))
FROM cart

demo on dbfiddle.uk

edited May 8, 2019 at 10:43

answered May 8, 2019 at 10:20

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON_REMOVE object in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related