Tutorial by Examples

CREATE TABLE table_name ( id INT NOT NULL AUTO_INCREMENT, json_col JSON, PRIMARY KEY(id) );

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 b...

This inserts a json dictionary where one of the members is an array of strings into the table that was created in another example. INSERT INTO myjson(dict) VALUES('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}'); ...

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','schevening...

This converts valid json strings to MySQL JSON type: SELECT CAST('[1,2,3]' as JSON) ; SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

JSON_OBJECT creates JSON Objects: SELECT JSON_OBJECT('key1',col1 , 'key2',col2 , 'key3','col3') as myobj; JSON_ARRAY creates JSON Array as well: SELECT JSON_ARRAY(col1,col2,'col3') as myarray; Note: myobj.key3 and myarray[2] are "col3" as fixed string. Also mixed JSON data: S...