Working with JSON data structures in MySQL

mysql_json.sql

-- https://www.digitalocean.com/community/tutorials/working-with-json-in-mysql

-- select rows where isPrivate field is ""
SELECT * FROM ecom_categories WHERE JSON_EXTRACT(`attributes` , '$.isPrivate') = "";

-- select rows where isPrivate field is not a part of JSON object
SELECT * FROM ecom_categories WHERE JSON_EXTRACT(`attributes` , '$.isPrivate') IS NULL;

-- add isPrivate field to attributes which are not present
UPDATE ecom_categories SET `attributes` = JSON_INSERT(`attributes`, '$.isPrivate', false);

-- remove isPrivate field from attributes
UPDATE ecom_categories SET `attributes` = JSON_REMOVE(`attributes`, '$.isPrivate');

-- conditional update of isPrivate field
UPDATE ecom_categories SET `attributes` = JSON_REPLACE(`attributes`, '$.isPrivate', true) WHERE type="MOBILE";