Skip to content

Select from a mariaDB table into JSON format ?

Can we output content of a mariaDB table into JSON using the CONNECT Storage Engine ?
MariaDB and MySQL are not currently JSON friendly databases. The usage of JSON in MySQL is almost inexistent with the excetion of the explain JSON output and of MariaDB dynamic column dump.
There also exist a few udf (in MySQL lab area) to help manipulate JSON strings(searching, modification …).

A basic question often raised is how to output the content of a MariaDB table in JSON format. Can the MariaDB CONNECT storage engine help do that ?

Suppose we have a simple table :

MariaDB > create table book
(
  id       integer,
  isbn     varchar(20),
  lang     varchar(20),
  subject  varchar(80),
  title    varchar(80),
  datepub  date
)  engine=innodb;

Then insert a few rows.

insert into book values (1,'9782212090819','fr','applications','Construire une application XML','2008-7-04');
insert into book values (1,'9782840825685','fr','applications','XML en Action','1999-7-04');

There is no direct way to query this innoDB table and get the result in a JSON format. Many users have written PHP(or other language) scripts to do this in a more or less generic way.
Let us create a CONNECT/JSON table based on this innoDB table.

MariaDB > create table bookj  
  engine=CONNECT table_type=JSON 
  file_name='/var/lib/mysql/json/bookj.jsn'  
  as select * from book;

This create table will produce a json file with the JSON representation of the table content :

bookj.jsn :
[
	{
		"id": 1,
		"isbn": "9782212090819",
		"lang": "fr",
		"subject": "applications",
		"title": "Construire une application XML",
		"datepub": 1215129600
	},
	{
		"id": 2,
		"isbn": "9782840825685",
		"lang": "fr",
		"subject": "applications",
		"title": "XML en Action",
		"datepub": 931046400
	}
]

But how can we directly request the JSON representation through a select ?
We will create a second CONNECT/JSON table based on the same underlying file produced by previous step.

create table bookj2 (
	ROW varchar(500) field_format='*'
)
engine=CONNECT table_type=JSON file_name='/var/lib/mysql/json/bookj.jsn';

The field_format='*' is a JSONPath meaning that we take the JSON as is and do not try to navigate properties.

MariaDB > select * from bookj2;
+------------------------------------------------------------------------------------------------------------------------------------+
| ROW                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------+
| {"id":1,"isbn":"9782212090819","lang":"fr","subject":"applications","title":"Construire une application XML","datepub":1215129600} |
| {"id":2,"isbn":"9782840825685","lang":"fr","subject":"applications","title":"XML en Action","datepub":931046400}                   |
+------------------------------------------------------------------------------------------------------------------------------------+

That is not really simple but that do the job. The structure produced is a flat structure without array or embeded JSON.
In the real world the equivalent of a JSON hierarchy is a set of tables linked through foreign keys. With MariaDB this can also include dynamic column for multi values columns.

Leave a Reply

Your email address will not be published. Required fields are marked *