Mysql with JSON

Let us create a table playes

CREATE TABLE `players` ( 
  `id` INT NOT NULL,
  `player_and_games` varchar(5000),
  PRIMARY KEY (`id`)
);


Insert JSON data into table

INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{ 
    "name": "Sally", 
    "games_played":{   
       "Battlefield": "yes",                   
                                                                       
                             
       "Crazy Tennis": "yes", 
       "Puzzler": {                             
                                                                       
                                                                       
                                                                       
                                                                        
                                                                 
          "time": 7
        }
      }
   }'
)

INSERT INTO `players` (`id`, `player_and_games`) VALUES (2, '{ 
    "name": "radhey", 
    "games_played":{   
       "Battlefield": "no",                   
                                                                       
                             
       "Crazy Tennis": "no", 
       "Puzzler": {                             
                                                                       
                                                                       
                                                                       
                                                                       
                                                                 
          "time": 7
        }
      }
   }'
) 


MySQL JSON Functions (MySQL 5.7 Reference Manual  /  ...  /  JSON Function Reference)
Table 12.20 JSON Functions
Name    Description
JSON_APPEND() (deprecated 5.7.9)        Append data to JSON document
JSON_ARRAY()
Create JSON array
JSON_ARRAY_APPEND()
Append data to JSON document
JSON_ARRAY_INSERT()
Insert into JSON array
->
Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS()
Whether JSON document contains specific object at path
JSON_CONTAINS_PATH()
Whether JSON document contains any data at path
JSON_DEPTH()
Maximum depth of JSON document
JSON_EXTRACT()
Return data from JSON document
->>
Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT()
Insert data into JSON document
JSON_KEYS()
Array of keys from JSON document
JSON_LENGTH()
Number of elements in JSON document
JSON_MERGE() (deprecated 5.7.22)       Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PATCH()
Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE()
Merge JSON documents, preserving duplicate keys
JSON_OBJECT()
Create JSON object
JSON_PRETTY()
Prints a JSON document in human-readable format, with each array element or object member printed on a new line, indented two spaces with respect to its parent.
JSON_QUOTE()
Quote JSON document
JSON_REMOVE()
Remove data from JSON document
JSON_REPLACE()
Replace values in JSON document
JSON_SEARCH()
Path to value within JSON document
JSON_SET()
Insert data into JSON document
JSON_STORAGE_SIZE()
Space used for storage of binary representation of a JSON document; for a JSON column, the space used when the document was inserted, prior to any partial updates
JSON_TYPE()
Type of JSON value
JSON_UNQUOTE()
Unquote JSON value
JSON_VALID()
Whether JSON value is valid
Name    Description

MySQL 5.7.22 and later supports two aggregate JSON functions JSON_ARRAYAGG() and JSON_OBJECTAGG(). See Section 12.19, “Aggregate (GROUP BY) Functions”, for descriptions of these.
Also beginning with MySQL 5.7.22:
•             “pretty-printing” of JSON values in an easy-to-read format can be obtained using the JSON_PRETTY() function.
•             You can see how much storage space a given JSON value takes up using JSON_STORAGE_SIZE().
For complete descriptions of these two functions, see Section 12.16.6, “JSON Utility Functions”.

Select Statement :

SELECT JSON_EXTRACT(player_and_games, '$.name') FROM players; 

The $ sign simply means to start the search at the top level of the JSON document. From there, we tell the function to search for the name key and return the result. We can keep going down the chain by adding more keys if they exist in the JSON document. For example, if we wanted to get the players who played "Puzzler" and their times, we'd write:

SELECT JSON_EXTRACT(player_and_games, '$.name') as player, JSON_EXTRACT(player_and_games, '$.games_played.Puzzler.time') as time FROM players WHERE JSON_EXTRACT(player_and_games, '$.games_played.Puzzler') > 0; 


SELECT JSON_SEARCH(player_and_games, "all", "Alfred") as name, id FROM players;

UPDATE players SET player_and_games = JSON_INSERT(player_and_games, '$.games_played.Puzzler', JSON_OBJECT('time', 20)) WHERE player_and_games->'$.name' = 'Henry';

Update command :


UPDATE players SET player_and_games = JSON_REPLACE(player_and_games, '$.games_played.Battlefield', 'no') WHERE player_and_games->'$.name'='Ali';

UPDATE players SET player_and_games = JSON_SET(player_and_games, '$.games_played.Battlefield', 'no', '$.games_played.Puzzler', JSON_OBJECT('time', 15)) WHERE id = 3;  

Delete command:

UPDATE players SET player_and_games = JSON_REMOVE(player_and_games, '$.games_played.Puzzler') WHERE id = 5;
 

 


Comments

Post a Comment

Popular posts from this blog

Getting started Mysql with JSON

Getting started Mysql with JSON Let us create a table playes CREATE TABLE `players` ( `id` INT NOT NULL , `player_and_games` varchar ( 5000 ), PRIMARY KEY ( `id` ) );     Insert JSON data into table    INSERT INTO `players` ( `id` , `player_and_games` ) VALUES ( 1 , '{ "name": "Sally", "games_played":{ "Battlefield": "yes", "Crazy Tennis": "yes", "Puzzler": { ...
Read more

How to get the parameter from url in codeigniter?

$this -> uri -> segment ( n ); where n = 1 for controller, n = 2 for method and n = 3 for parameter and so on. You need n = 3 to get parameter. In your path localhost/log/job/php , your method name is missing. Even if your method name is index then you route will be localhost/log/job/index/php In case if you need to remove index.php from url then you will get parameter using localhost/log/index.php/job/index/php To remove index.php you need to create .htaccess file by following these steps: Create a .htaccess file where index.php file is located with content RewriteEngine on RewriteCond %{ REQUEST_FILENAME } !- f RewriteCond %{ REQUEST_FILENAME } !- d RewriteRule ^(.*) $ index . php / $1 [ L , QSA ] Make sure that apache can access this .htaccess file. To do this edit apache configuration file. If you use ubuntu, then it is /etc/apache2/sites-available/default and then change AllowOverride none to AllowOverride all for directory and www dir...
Read more

MySQL event scheduler and how to create MySQL events to automate database tasks

Working with MySQL Scheduled Event  SET GLOBAL event_scheduler = OFF;  SET GLOBAL event_scheduler = ON; MySQL event scheduler and how to create MySQL events to automate database tasks. A MySQL event is a task that runs based on a predefined schedule therefore sometimes it is referred to as a scheduled event. MySQL event is also known as “temporal trigger” because it is triggered by time, not by table update like a trigger. A MySQL event is similar to a cron job in UNIX or a task scheduler in Windows. You can use MySQL events in many cases such as optimizing database tables, cleaning up logs, archiving data, or generate complex reports during off-peak time. MySQL event scheduler configuration MySQL uses a special thread called event schedule thread to execute all scheduled events. You can see the status of event scheduler thread by executing the following command: SHOW PROCESSLIST; By default, the event scheduler thread is not enabled. To enable and start the event scheduler t...
Read more