How to Insert JSON Data into a MySQL Table

Insert JSON Data into a Column

Write a MySQL query to insert a new record into a MySQL table that includes a JSON column containing semi-structured data.

Solution:

-- Insert a new record into the Products table with JSON data in the Details column.
INSERT INTO Products (ProductName, Details)
VALUES ('Smartphone', '{"brand": "TechCorp", "model": "X100", "specs": {"ram": "4GB", "storage": "64GB"}}');

Explanation:

• Purpose of the Query:
• To demonstrate how to insert a record with a JSON value into a table.
• This illustrates the use of JSON data types for storing semi-structured information.
• Key Components:
• INSERT INTO Products (ProductName, Details) : Specifies the table and columns for insertion.
• VALUES ('Smartphone', '{...}') : Inserts the product name and a JSON document with details.
• Real-World Application:
• Useful for applications that need to store variable or complex data such as product specifications.

Notes:

• Ensure the target column (Details) is defined with the JSON data type in the table schema.
• JSON data allows for flexibility in storing nested and varying data structures.

For more Practice: Solve these Related Problems:

• Write a MySQL query to insert a new record with a JSON column that contains a nested array of categories.
• Write a MySQL query to insert a record with JSON data including special characters and escape sequences.
• Write a MySQL query to perform a bulk insert of multiple rows where one column stores JSON data with varying structures.
• Write a MySQL query to insert a record where the JSON column contains dynamic keys generated from other column values.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: JSON and NoSQL Features Home.
Next MySQL Exercise: Query JSON Data with JSON_EXTRACT.