Tampa Tampa - 1 year ago 88
JSON Question

MySQL, json, JSON_ARRAY_APPEND - why will append not work on 5.7.13?

How on earth do I insert data into a json mysql field.

I can create a record

INSERT INTO git_object (user_id,git_meta,last_update) VALUES ('11111','[{"host": "a", "id": "1"}]',(select now()));

How do I append to the array.

SELECT JSON_ARRAY_APPEND(git_meta, '$', '{"host": "b"}') FROM git_object where user_id='11111'

I tried the above. What do I get with the below?

SELECT * FROM git_object;

'[{"id": "1", "host": "a"}]'

What am I doing wrong

MySQL Version '5.7.13'

Answer Source

To modify data stored in any table you need to use the update sql command, not select.

Update git_object
set  git_meta=JSON_ARRAY_APPEND(git_meta, '$', '{"host": "b"}')
where user_id='11111'