MySQL handles JSON common functions

MySQL supports JSON format in the new version. Use this article to document how to use JSON in MySQL. Some common manipulation functions.

For more information please refer to the official documentation dev.mysql.com/doc/refman/…

Create a table in JSON format

CREATE TABLE `t1` (
  `id` INT NOT NULL,
  `f1` VARCHAR(45) NULL,
  `f2` JSON NULL.PRIMARY KEY (`id`)
);
Copy the code

Operations on JSON fields

1. Plain insert

INSERT INTO `t1` VALUES (1.'1'.'{"a":1, "b":"2"}');
Copy the code

2. Built-in functions

JSON_OBJECT

Insert json object into database
INSERT INTO `t1` VALUES (1.'1'.JSON_OBJECT("a", 1, "b", "2"));
Copy the code

JSON_ARRAY

Insert json array into database
INSERT INTO `t1` VALUES (2.'2'.JSON_ARRAY("arr", 1.2));
Copy the code

JSON_MERGE

Merge two JSON files
INSERT INTO `t1` VALUES (
	3.'3',
  JSON_MERGE(
  	'{"a":3, "b":"3"}'.'{"c":3, "d":"3"}'));- the method 2
INSERT INTO `t1` VALUES (
	4.'4',
  JSON_MERGE(
  	JSON_OBJECT("a", 4, "b", "4"),
    JSON_OBJECT("c", 4, "d", "4")
  )
);

3 - method
INSERT INTO `t1` VALUES (
	5.'5',
  JSON_MERGE(
  	'{"a":5, "b":"5"}'.JSON_OBJECT("c", 5, "d", "5")
  )
);
Copy the code

JSON_EXTRACT

Get the value of a field in JSON
Get the value under base in F2
SELECT id,JSON_EXTRACT(f2, '$.base') FROM `t1` WHERE id = 1;
Get the first value of namelist (jsonArray) under base in F2
SELECT id,JSON_EXTRACT(f2, '$.base.namelist[0]') FROM `t1` WHERE id = 1;
-- Simplified form
SELECT f2 FROM `t1` where f2->'$.base' = 2;
Copy the code

JSON_INSERT

Add a key-value to the field. If the key exists, nothing is done

Select * from f2 where id=1 and add age
UPDATE `t1` SET f2 = JSON_INSERT(f2, '$.age'.10) WHERE id = 1;
Copy the code

JSON_REPLACE

Replace the content. If the key does not exist, no operation is performed

Select * from f2 where id=1 and age = 9
UPDATE `t1` SET f2 = JSON_REPLACE(f2, '$.age'.9) WHERE id = 1;
Copy the code

JSON_SET

If the key exists, the value value is replaced; if the key does not exist, the key-value value is added

Select * from f2 where id=1 and age = 8
UPDATE `t1` SET f2 = JSON_REPLACE(f2, '$.age'.8) WHERE id = 1;
Copy the code

JSON_REMOVE

Delete the content

Select age from f2 where id=1
UPDATE `t1` SET f2 = JSON_REMOVE(f2, '$.age') WHERE id = 1;
Copy the code