I would like to export the data of my database to json format below, had already tried but i can only get to show 1 table and not the whole with reference.
CREATE TABLE generic (
id INTEGER NOT NULL PRIMARY KEY,
errorCode INT,
errorMsg TEXT,
PRIMARY KEY (id)
);
CREATE TABLE generic_data (
generic_id TEXT,
id INT,
name TEXT,
ovpn TEXT,
category TEXT,
PRIMARY KEY (id),
FOREIGN KEY (generic_id) REFERENCES generic(id)
);
CREATE TABLE generic_data_children (
generic_data_id INT,
id INT,
name TEXT,
config TEXT,
PRIMARY KEY (id),
FOREIGN KEY (generic_data_id) REFERENCES generic_data(id)
);
Here is my database info with the table columns for reference
Expected Json Format:
{
"data": [{
"children": [{
"id": 1,
"name": "Default",
"config": "config1"
}, {
"id": 2,
"name": "sample",
"config": "config2"
}, {
"id": 3,
"name": "sample2",
"config": "config3"
}, {
"id": 4,
"name": "testnetwork",
"config": "config4"
}, {
"id": 5,
"name": "sample5",
"config": "config5"
}],
"id": 1,
"name": "PH-Philippines 1",
"ovpn": "testovpn",
"category": "Private"
}],
"errorCode": 0,
"errorMsg": ""
}
You can use JSON_ARRAY(), GROUP_CONCAT() and JSON_OBJECT() functions together as
SELECT JSON_PRETTY(
REPLACE(
REPLACE(
REPLACE(
JSON_OBJECT(
'errorCode', g.errorCode,
'errorMsg', COALESCE(g.errorMsg,""),
'data',JSON_ARRAY(
(SELECT
JSON_OBJECT('id',gd.id,
'name',gd.name,
'ovpn',gd.ovpn,
'children',
JSON_ARRAY(
GROUP_CONCAT(
JSON_OBJECT('name',gdc.name,
'id',gdc.id,
'config',gdc.config)
) )
)
FROM generic_data_children gdc
LEFT JOIN generic_data AS gd
ON gd.id = gdc.generic_data_id
WHERE generic_data_id = g.id
)
)
)
,'\\"','"')
,'"{','{')
,'}"','}')
) AS 'New JSON'
FROM generic g
Using REPLACE() functions are needed at the end in order to format related to double-quotes.
Btw, get rid of PRIMARY KEY repetition for the first table, and convert data type of generic_id column to INT within the second table.