CRUD Query using JSON Column

Requirements: MySQL 5.7.9

SELECT examples:


mysql> SELECT *
FROM users
WHERE meta_data->"$.first_name" = 'bob'

 

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:

mysql> SELECT post_data_json->>"$._price" AS price
FROM wp_posts
WHERE post_data_json->>"$.post_title" LIKE '%Ninja%'

 

Select data from within sub objects:3

mysql> SELECT meta_data->>"$.address.tel"
FROM users
WHERE meta_data->>"$.address.street" = "123 Main St"

 

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

mysql> SELECT post_data_json->>"$._price" AS price
FROM wp_posts
WHERE lower(post_data_json->>"$.post_title") LIKE '%ninja%'

 

UPDATE and DELETE examples:


Update a specific key:

mysql> UPDATE wp_posts
SET post_data_json = JSON_SET(post_data_json, "$.post_title", "I am ID1")
WHERE ID = 1;

 

Update multiple keys:

mysql> UPDATE wp_posts
SET post_data_json = JSON_SET(post_data_json, "$.post_title", "I am ID 01", "$.post_name", "I-am-ID-01")
WHERE ID = 1;

 

Append a new key-value pair to existing JSON:

mysql> UPDATE wp_posts
SET post_data_json = JSON_SET(post_data_json, "$.something", "I am something")
WHERE ID = 1;

 

Delete key-value pair from existing JSON:

mysql> UPDATE wp_posts
SET post_data_json = JSON_REMOVE(post_data_json, "$.something")
WHERE ID = 1;

 

INSERT examples:


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

mysql> INSERT INTO person
VALUES ('{"pid": 101, "name": "name1"}');


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

Contact Me