How to create and insert a JSON object using MySQL queries?

Question

I'm trying to create a JSON object and then read the values from it into MySQL table. But I'm facing errors and I'm new to both JSON and MySQL.

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);

OPENJSON is used in sqlserver (T-SQL), you tagged your question mysql. Wich are you using ? — Daniel E.
– Daniel E., Commented Dec 2, 2016 at 11:19

Ben in CA · Accepted Answer · 2021-03-04 22:50:04Z

71

On creating table set your field as JSON datatype.

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

And Insert JSON data into it,

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

Or Insert JSON data by Key:Value

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

Select JSON data,

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

Note: Only supported by MySQL 5.7 (or higher) using InnoDB.

edited Mar 4, 2021 at 22:50

Ben in CA

89916 silver badges26 bronze badges

answered Dec 2, 2016 at 11:24

saravanabawa

1,7851 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rj_23495 Over a year ago

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'json DEFAULT NULL, )' at line 2" this is the error i am encountering

saravanabawa Over a year ago

MySQL 5.7 InnoDB or higher versions only supports.

Collectives™ on Stack Overflow

How to create and insert a JSON object using MySQL queries?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related