Creating and Inserting JSON Objects in MySQL Tables

When working with JSON data in MySQL, you may encounter challenges if you're unfamiliar with the process. Here's a step-by-step guide to assist you.

Creating a Table with JSON Data Type

To store JSON data effectively, you'll need to define your table columns with the JSON datatype. For instance:

CREATE TABLE `person` (
  `name` JSON DEFAULT NULL
);

Inserting JSON Data

There are multiple ways to insert JSON data into your MySQL table:

1. Inserting JSON Arrays

To insert an array of values, enclose them in square brackets and use the JSON_CONTAINS function in your query:

INSERT INTO `person` (`name`)
VALUES ('["name1", "name2", "name3"]');

2. Inserting JSON Objects (Key: Value Pairs)

To insert individual JSON objects, use curly braces to enclose your key-value pairs:

INSERT INTO person VALUES ('{"pid": 101, "name": "name1"}');
INSERT INTO person VALUES ('{"pid": 102, "name": "name2"}');

Selecting JSON Data

Once you've inserted your JSON data, you can retrieve specific records using the JSON_CONTAINS function:

SELECT * FROM `person` WHERE JSON_CONTAINS(name, '["name1"]');

Note: These features require MySQL version 5.7 or higher and InnoDB storage engine.