How To Use JSON Data Field In MySQL Database

In this article, we will see how to use json field in mysql database. JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write.

So, let's see MySQL JSON data type example, how to store JSON data in MySQL, how to save JSON data in the database, JSON data type in MySQL, laravel 8 store JSON in the database, laravel update JSON column, save data in JSON format in laravel.

here, we are creating a post table with a category JSON column.

CREATE TABLE `post` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(200) NOT NULL,
  `category` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

 

Add JSON Data

JSON data can be passed in INSERT or UPDATE statements. For example, our post category can be passed as an array (inside a string).

INSERT INTO `post` (`name`, `category`)
VALUES (
  'web developing',
  '["JavaScript", "PHP", "JSON"]'
);

 

 

JSON can also be created with these.

JSON_ARRAY()  function create arrays like this.

-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

 

JSON_OBJECT() function create objects like this :

-- returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);

 

JSON_QUOTE() function quotes a string as a JSON value.

-- returns "[1, 2, \"abc\"]":
SELECT JSON_QUOTE('[1, 2, "abc"]');

 

JSON_TYPE() function allows you to check JSON value types. It should return OBJECT, ARRAY, a scalar type (INTEGER, BOOLEAN, etc), NULL, or an error

-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');

 

 

JSON_VALID() function returns 1 if the JSON is valid or 0 otherwise.

-- returns 1:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns 1:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns 0:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Note: If you insert invalid JSON data then it will create an error and the whole record will not be inserted/updated.

 


You might also like :

RECOMMENDED POSTS

FEATURE POSTS