How Can I Create and Insert JSON Objects into MySQL Tables?

Creating and Inserting JSON Objects Using MySQL Queries

Suppose you're attempting to create JSON objects and read their values into MySQL tables but encounter errors. The provided code seeks to create a JSON object and use it to populate the Person table:

SET @j = '{"key1": "value1", "key2": "value2"}';
CREATE TABLE Person (name int,id int);
INSERT INTO Person (name,id) SELECT * FROM OPENJSON(@j) WITH (name int,id int);

To resolve these errors, follow these steps:

1. Define JSON Data Type for Table Columns:
   Create the Person table with a name column of the JSON data type.

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

2. Insert JSON Values:
   Two options are available:

   • Array Syntax: Insert JSON data as an array using square brackets.

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

   • Key:Value Syntax: Insert JSON data using curly brackets and key-value pairs.

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

3. Select JSON Data:
   Retrieve JSON data using a query that checks for matches within the name column.

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

Note that this functionality is only supported in MySQL versions 5.7 or higher using InnoDB.

The above is the detailed content of How Can I Create and Insert JSON Objects into MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!