MYSQL JSON
Create simple table with a primary key and JSON field
CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
json_col JSON,
PRIMARY KEY(id)
);Insert a simple JSON
INSERT INTO
table_name (json_col)
VALUES
('{"City": "Galle", "Description": "Best damn city in the world"}');That's simple as it can get but note that because JSON dictionary keys have to be surrounded by double quotes the entire thing should be wrapped in single quotes. If the query succeeds, the data will be stored in a binary format.
Updating a JSON field
In the previous example we saw how mixed data types can be inserted into a JSON field. What if we want to update that field? We are going to add scheveningen to the array named variations in the previous example.
UPDATE
myjson
SET
dict=JSON_ARRAY_APPEND(dict,'$.variations','scheveningen')
WHERE
id = 2;Notes:
1. The $.variations array in our json dictionary. The $ symbol represents the json documentation. For a full
explaination of json paths
2. Since we don't yet have an example on querying using json fields, this example uses the primary key.
Now if we do SELECT * FROM myjson we will see
+----+-----------------------------------------------------------------------------------------+
| id | dict |
+---+-----------------------------------------------------------------------------------------+
| 2 | {"opening": "Sicilian", "variations": ["pelikan", "dragon", "najdorf", "scheveningen"]} |
+----+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)