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