Question

0

143

Vistas

Export MySQL Database with 3 tables to json format via php

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": ""
}

9 months ago · Santiago Trujillo

1 Respuestas

Responde la pregunta

0

Responde la pregunta

Encuentra empleos remotos

Answer 1 · 2022-05-11T21:44:43.466Z

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

Demo

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.