MySQL JSON Type Constraint Error on Insert?

Question

Here is my snippet creating db and table example, somehow i could find what causing the error and already looking for the answer and found nothing so far. Any help?

MariaDB [(none)]> use db1
Database changed

MariaDB [db1]> create table user(id int not null, profile json);
Query OK, 0 rows affected (0.028 sec)

I tried this insert query and get error

MariaDB [db1]> insert into user(`id`, `profile`) values(1, '{name: "irvan"}');
ERROR 4025 (23000): CONSTRAINT `user.profile` failed for `db1`.`user`

MariaDB [db1]> INSERT INTO user(`id`, `profile`) VALUES(1, '{name: "irv" }');
ERROR 4025 (23000): CONSTRAINT `user.profile` failed for `db1`.`user`

MariaDB [db1]> INSERT INTO user(`id`, `profile`) VALUES(1, '');
ERROR 4025 (23000): CONSTRAINT `user.profile` failed for `db1`.`user`

FYI, here the version of mysql that i'm using

C:\xampp\mysql\bin>mysql -V
mysql  Ver 15.1 Distrib 10.4.27-MariaDB, for Win64 (AMD64), source revision 
0946c99e7d6f7ac9dfcf3e60dae6ae85161d5ef2

Answer 1

Json has some formal requirements, which means that you need correct jsons

create table user(id int not null, profile json);

INSERT INTO user(`id`, `profile`) VALUES(1, '{"name" : "irv" }');

insert into user(`id`, `profile`) values(1, '{"name" : "irvan"}');

INSERT INTO user(`id`, `profile`) VALUES(1, '{}');

SELECT * FROM user

id	profile
1	7b226e616d6522203a202269727622207d
1	7b226e616d6522203a2022697276616e227d
1	7b7d

fiddle

to get a readable answer you need to convert it to CHAR, like Akina posted sample