JSON_OBJECT

It is a list of key-value pairs and returns a JSON object.

Create a table with a column of data type JSON

CREATE TABLE Customer (
  id int unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  email varchar(100) NOT NULL,
  address JSON NOT NULL
);

To create a column with JSON data type assign it as JSON.

Insert values into table

INSERT INTO Customer (name, email, address) VALUES ('Cherish', 'cherish@gmail.com', '{"name": "customerLocation1", "address1": "320 Sector-12F", "address2": "Panaji, Goa", "province": "Goa", "country": "India" }');
INSERT INTO Customer (name, email, address) VALUES ('Clark', 'clark@gmail.com', '{"name": "customerLocation2", "address1": "500 Sector-10F", "address2": "Vaishali, Ghaziabad", "province": "Ghaziabad", "country": "India" }');
INSERT INTO Customer (name, email, address) VALUES ('Peter', 'peter@gmail.com', '{"name": "customerLocation1", "address1": "320 Sector-12F", "address2": "California, USA", "province": "California", "country": "USA" }');

Note: All values should be single-quoted and key-value pairs should be double-quoted.

Select operations on table

Select all data from the table

SELECT * FROM Customer;

Select custom JSON objects from table

SELECT JSON_OBJECT(
  'id', -- key
  cs.id, -- value
  'name',  
  cs.name, 
  'email', -- key
  cs.email, -- value
  'address', 
  cs.address 
) FROM Customer cs
WHERE id = 3;

Select JSON_TYPE

SELECT JSON_TYPE(address) FROM Customer;

Select specific key-value from JSON Object

This can be done in two ways:

• inline path operator ->>
• column-path operator ->

SELECT address->>"$.address1", address->"$.address1" FROM Customer;

Note: Inline-path operator leave surrounding quote marks but the Column-path operator includes them.

You've done it! Good Job!

Now you have complete knowledge of JSON_OBJECT. Try to do hands-on practice with it to gain full confidence.