Tampa Tampa - 4 months ago 22x
JSON Question

MySQL why this happends ob json insert? "{\"host\": \"c\"}"

Whats the deal with mysql and json? It makes no sense.

1) This is how I insert data for first insert into a record

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

Here is the result:

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

Now I want to append into the array:

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

Here are the results:

11111, [{"id": "1", "host": "a"}, "{\"host\": \"c\"}"]

So..what is the deal with the "\" in the last insert?

This wrecks havoc from calling mysql from python because the first element when I decode as json will return a python hash while the other is a string.

Why does this happen?


The function JSON_ARRAY_APPEND() does not attempt to parse as JSON the values it receives as arguments.

SET git_meta = JSON_ARRAY_APPEND(git_meta, '$', '{"host": "c"}')

You pass it a string as its third argument, it appends a string to the array stored in the git_meta column. This is why the quotes are escaped.

There are two simple ways to achieve your goal:

  1. wrap the object you want to append into an array and use JSON_MERGE() to combine the JSON documents:

    SET git_meta = JSON_MERGE(git_meta, '[{"host": "c"}]')
  2. use function JSON_OBJECT() to create a JSON document that contains the object you need and pass this object to JSON_ARRAY_APPEND() as the value to put in the JSON column:

    SET git_meta = JSON_ARRAY_APPEND(git_meta, '$', JSON_OBJECT('host', 'c'))