CODEWITHSUNDEEP
mysqljson
Marius
Marius

Reputation: 597

MySQL appending to a JSON field

I'm trying to write a query that would append JSON values to a JSON field in my database. I have a table called cart with three fields inside: id type int, status type varchar and items type json. So basically i'm trying to write a query where it would find my cart by the id and it would add an item to the end of my item field so far what i have is this: 

SELECT  cart set items= JSON_ARRAY_APPEND(@items, '$', {"item_id": "5"}) where id = 1

I know this is not enough since it's not working for me, so could anyone help me? I'm kinda stuck right now.

Upvotes: 0

Views: 1775

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522516

Actually, your current code is not far off, except you need to use UPDATE rather than SELECT. I was only able to get correct behavior by using JSON_OBJECT to represent the key value pair which you want to add to the top level array. Consider the following update query:

UPDATE cart
SET items = JSON_ARRAY_APPEND('[{"item_id": "10"}]', '$', JSON_OBJECT('item_id', '5'))
WHERE id = 1;

Demo

Upvotes: 2

Related Questions