Formatting the result of an SQL query into JSON

Question

I have a small database as defined in the code snippet below. I want to query this to get all of the information and send it to an vue app via a JSON file via a Flask API. At the moment the query that I am using is

SELECT tbl_room.room, tbl_room.room_id, tbl_device.name, tbl_display.display, tbl_function.function, tbl_device.format
    FROM tbl_device
    INNER JOIN tbl_room ON tbl_room.id = tbl_device.room_id
    INNER JOIN tbl_display ON tbl_display.id = tbl_device.display_id
    INNER JOIN tbl_function ON tbl_function.id = tbl_device.function_id
    ORDER BY tbl_room.room_id;

this gives me an output like:

Bedroom (Main)  bedroom_main    bme280/1    gauge   temperature {"min": 0, "max": 50, "dp": 1, "units": "&deg;C"}
Bedroom (Main)  bedroom_main    bme280/1    gauge   humidity    {"min": 0, "max": 100, "dp": 1, "units": "%"}
Bedroom (Main)  bedroom_main    bme280/1    gauge   pressure    {"min": 0, "max": 1100, "dp": 1, "units": "hPa"}
Front Room  front_room  ds18b20/heater  gauge   temperature {"min": 0, "max": 50, "dp": 1, "units": "&deg;C"}

I would like to get it into a JSON file so that it is arranged as:

[
    { "name": "Office",
      "id": "office",
      "devices": []
    },
    { "name": "Front Room", 
       "id": "front_room",
       "devices": []
    }
  }
]

Can this be done in a single sql query? Or do I have to do a query for each room in a loop? Or is it more efficient to dump the whole dataset out in one query and process it in pyhton afterwards? This is a small dataset but I'm interested to know which is the most efficient method.

Thank you in advance, Martyn

Here is my table structure:

-- Table: tbl_device
CREATE TABLE tbl_device (
    name        VARCHAR NOT NULL ON CONFLICT ROLLBACK,
    room_id     INTEGER CONSTRAINT fk_room REFERENCES tbl_room (id) 
                        NOT NULL,
    function_id INTEGER CONSTRAINT fk_function REFERENCES tbl_function (id) 
                        NOT NULL ON CONFLICT ROLLBACK,
    display_id  INTEGER CONSTRAINT fk_display REFERENCES tbl_display (id) 
                        NOT NULL ON CONFLICT ROLLBACK,
    format      VARCHAR NOT NULL ON CONFLICT ROLLBACK
                        DEFAULT [default],
    UNIQUE (
        name,
        room_id,
        function_id,
        display_id
    )
    ON CONFLICT ROLLBACK
);


-- Table: tbl_display
CREATE TABLE tbl_display (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    display VARCHAR NOT NULL ON CONFLICT ROLLBACK
                    UNIQUE ON CONFLICT ROLLBACK
);


-- Table: tbl_function
CREATE TABLE tbl_function (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    function VARCHAR NOT NULL ON CONFLICT ROLLBACK
                     UNIQUE ON CONFLICT ROLLBACK,
    control  BOOLEAN NOT NULL
                     DEFAULT (0) 
);


-- Table: tbl_room
CREATE TABLE tbl_room (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id VARCHAR NOT NULL
                    UNIQUE ON CONFLICT ROLLBACK,
    room    VARCHAR NOT NULL ON CONFLICT ROLLBACK
);

Mysql does not support on conflict clause, so you cannot possibly use mysql. — Shadow, Jul 08 '20 at 07:23

score 0 · Answer 1 · answered Jul 08 '20 at 07:28

First, There is no way to directly feed JSON response from MySQL database to VueJS or any other App. VueJS App is the fronted of your application. You have to create a Backend which connects to MySQL database, fetch necessary data from MYSQL Database, Convert them to JSON and send to Vue App.

To Develop a backend, you may use languages such as PHP, Python, Java, NodeJS etc.

If you can continue with PHP, it is very easy to fetch data and convert to JSON.

But If you still need to continue with Python, you have to use Flask or any other python web framework to do that.

Here is the sample php code

<?php

$dbhost = 'hostname';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'database';

  $db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

  if ($db->connect_errno) {
     printf("Failed to connect to database");
     exit();
  }

  $result = $db->query("SELECT * FROM "); // Your SQL query

  $data = array();

  while ( $row = $result->fetch_assoc())  {
    $data[]=$row;
  }


 echo json_encode($data);
 
?>

Yes, I realised that I need a backend - I should have perhaps mentioned that in the question. I already have a flask application running which runs the query upon request by the front end app. I was just looking at the most effective way of querying the data in my back end. I have edited the question. — MartynW, Jul 08 '20 at 07:42
Please see this post https://stackoverflow.com/questions/43796423/python-converting-mysql-query-result-to-json It has explained a simple and efficient way to convert mysql fetched data to json in Flask — Shanaka Anuradha, Jul 08 '20 at 07:46
Thanks, this does not address how to group the json in the way that I need though. This will just convert each row of the query into a json array. — MartynW, Jul 08 '20 at 07:48

score 0 · Answer 2 · answered Jul 08 '20 at 08:56

If your version of sqlite was compiled with the JSON1 extension, enabled, something like:

SELECT json_group_array(json_object('name', tbl_room.name,
                                    'id', tbl_room.room_id,
                                    'devices', json_array()))
FROM tbl_room
GROUP BY tbl_room.name, tbl_room.room_id;

Formatting the result of an SQL query into JSON

2 Answers2