How to Use JSON Data Field in MySQL Database

Websolutionstuff | Jun-04-2021 | Categories : PHP MySQL

Today, In this post we will see how to use json field in mysql database. In this tutorial i will give mysql json data type example and we will see how to store json data in mysql using php.

here we will see differents type of json functions with example and we will see how to insert json data into mysql using php.

here, we are creating category table.

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 ex, 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');

 

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

 

Recommended Post
Featured Post
How To Image Upload Using Ajax In Laravel 9
How To Image Upload Using Ajax...

In this article, we will see how to image upload using ajax in laravel 9. Here, we will learn about image upload in...

Read More

Feb-07-2023

How To Integrate Paypal Payment Gateway In Laravel
How To Integrate Paypal Paymen...

In this tutorial I will teach you the most important topic of how to integrate the PayPal payment gateway in larave...

Read More

Jul-22-2020

How To Validate Form In React JS
How To Validate Form In React...

In this article, we will see how to validate a form in react js. We will validate the input type email, phone numbe...

Read More

Sep-07-2022

How To Create Candlestick Chart In Laravel 9 Using Highcharts
How To Create Candlestick Char...

In this article, we will see how to create a candlestick chart in laravel 9 using highcharts. A candlestick is a ty...

Read More

Oct-06-2022