add

Insert a string in JSON format, either as an object or as an array

INSERT INTO `a_goods` (`type`, `attrs`)
VALUES (' [" new product ", "popular", 1, "0"] ', '{" name ":" zhang ", "age" : 20, "strage" : "20", "data" : [1, "0"]}')

MySQL also has special functions JSON_OBJECT, JSON_ARRAY to generate data in JSON format, but be careful when using it, try to write in between

//JSON_OBJECT must be even and must be nested with JSON_ARRAY
INSERT INTO `a_goods` (`attrs`, `type`)
VALUES (
JSON_OBJECT (" name ", "zhang", "age", 20, "strage", "20", "data", JSON_ARRAY (" new product ", "popular", 1, "0")),
JSON_ARRAY(" New "," hot selling ",1,"0")
)

query

The string type category-> ‘$.name’ also contains double quotes. This is not the desired result. You can remove the double quotes using the JSON_UNQUOTE function. From MySQL 5.7.13 it is also possible to use the * ->> * operator, which is equivalent to JSON_UNQUOTE

Query the JSON value, that is, the value of the key

SELECT attrs->'$.name' as name, JSON_UNQUOTE(attrs->'$.name'), attrs->>'$.name' FROM a_goods
/ *
SELECT
Attrs ->'$.name' as name, query the value of name in JSON data and assign it to name
JSON_UNQUOTE(attrs->'$.name'),
attrs->>'$.name'
FROM a_goods
* /

Where statement in JSON

SELECT * FROM a_goods WHERE type = CAST('[" new "," hot selling ",1,"0"]' as JSON)
// Querying whether JSON exists in the database is the same as' where a=1 ', but the a has to be converted
// The CAST function is used to explicitly CAST an expression of one data type to another

SELECT * FROM a_goods WHERE attrs->>'$.age' = '20'
// It's important to note that element search in JSON is strictly variable type specific. For example, integers and strings are strictly variable type specific, i.e., "20" and "20"

SELECT * FROM a_goods WHERE JSON_CONTAINS(attrs, '20', '$.age')
SELECT * FROM a_goods WHERE JSON_CONTAINS(type, "new product "')
// Use the JSON_CONTAINS function, but somewhat contrary to the *column->path * form, the second parameter of JSON_CONTAINS does not accept integers, whether the JSON element is an integer or a string, otherwise an error will occur

update

UPDATE a_goods SET type = '["demo","0",1]'

JSON_INSERT() inserts a new value but does not overwrite an existing value

UPDATE a_goods SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;

JSON_SET() inserts the new value and overwrites the existing value

UPDATE a_goods SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;

JSON_REPLACE() replaces only existing values

UPDATE a_goods SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE i

JSON_REMOVE() Removes the JSON element

UPDATE a_goods SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;