Load JSON into MariaDB

TL;DR

MariaDB [test]> create table test01 (
    -> id int not null auto_increment,
    -> val json not null,
    ->    primary key (id)
    -> );
Query OK, 0 rows affected (0.004 sec)

MariaDB indicates that the JSON datatype is an alias for LONGTEXT. I considered adding a check constraint to ensure the insertion of valid JSON only. However, it turns out that this validation is performed by default with the JSON datatype.

MariaDB [test]> show create table test01;
+--------+------------------------------------------------------------------------------------+
| Table  | Create Table                                                                       |
+--------+------------------------------------------------------------------------------------+
| test01 | CREATE TABLE `test01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`val`)),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
+--------+------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> insert into test01(val) values(load_file('/tmp/file01.json'));
Query OK, 1 row affected (0.003 sec)

MariaDB [test]> select * from test01;

MariaDB [test]> select json_valid(val) from test01;

MariaDB [test]> select json_length(val) from test01;

MariaDB [test]> select json_extract(val,'$[0]') from test01;

MariaDB [test]> select json_value(json_extract(val,'$[0]'),'$.first_name') from test01;

Reference: https://blog.yannickjaquier.com/mariadb/how-to-load-json-into-mariadb.html

Feedback

Name

Email



Send