MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL JSON

From WikiOD

As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. https://dev.mysql.com/doc/refman/5.7/en/json.html

Remarks[edit | edit source]

Starting from MySQL 5.7.8, MySQL ships with a JSON type. Lots of devs have been saving JSON data in text columns for a log time but the JSON type is different, the data is saved in binary format after validation. That avoids the overhead of parsing the text on each read.

Create simple table with a primary key and JSON field[edit | edit source]

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

Insert a simple JSON[edit | edit source]

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[edit | edit source]

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 recognized by mysql refer to https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html
  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)

Insert mixed data into a JSON field.[edit | edit source]

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"]}');

Note, once again, that you need to be careful with the use of single and double quotes. The whole thing has to be wrapped in single quotes.

CAST data to JSON type[edit | edit source]

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);

Create Json Object and Array[edit | edit source]

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:

 SELECT JSON_OBJECT("opening","Sicilian", "variations",JSON_ARRAY("pelikan","dragon","najdorf") ) as mymixed ;

Credit:Stack_Overflow_Documentation