CRUD Query using JSON Column

Requirements: MySQL 5.7.9

SELECT examples:


 

You might notice your json data results are “quoted”. You could use JSON_UNQUOTE,
or you could use this “>>”, which is a shortcut of JSON_EXTRACT & JSON_UNQUOTE:

 

Select data from within sub objects:3

 

Search for strings like with the use of lower() function:

 

UPDATE and DELETE examples:


Update a specific key:

 

Update multiple keys:

 

Append a new key-value pair to existing JSON:

 

Delete key-value pair from existing JSON:

 

INSERT examples:


There’s no special function to use for creating a json object, just do the traditional SQL style:



Do you need help with a project? or have a new project in mind that you need help with?

Contact Me