In this article, we'll explore how to use JSON fields in your MySQL database. JSON, or JavaScript Object Notation, is a versatile data format that's easy to work with.

So, let's dive into examples of using the MySQL JSON data type, saving JSON data in your database, and even implementing it in Laravel for tasks like storing JSON-formatted data in your application. We'll focus on 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;

 

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_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.

 

In conclusion, incorporating JSON fields in your MySQL database can significantly enhance your data storage capabilities. This approach allows you to work with structured data in a flexible and efficient manner, making it an excellent choice for various applications.

Whether you're building a custom solution or utilizing a framework like Laravel, understanding how to use JSON fields will empower you to handle data more effectively and take full advantage of the database's capabilities.

 

---

You might also like:

• Read Also: Laravel Signature Pad Example
• Read Also: Laravel 8 cURL HTTP Request Example
• Read Also: Scrolla - jQuery Plugin for Reveal Animations
• Read Also: How To Upload File On The FTP Server Using PHP