Querying MySQL Table to Return JSON

Question

I have a MySQL table with temperature sensor data and I'm trying to get it's data into a JSON array for output to a PHP page with a JavaScript chart.

This is my table and some sample data:

+----+----------+---------------------+-------+
| id | sensorId |    dateRecorded     | tempF |
+----+----------+---------------------+-------+
|  1 |        1 | 2018-03-31 10:00:00 |  34.2 |
|  2 |        2 | 2018-03-31 10:00:00 |  83.1 |
|  3 |        2 | 2018-03-31 10:05:00 |  44.5 |
|  4 |        1 | 2018-03-31 10:05:00 |  65.2 |
+----+----------+---------------------+-------+

The JSON structure I'm trying to get as output is this:

data: [
    { dateRecorded: '2018-03-31 10:00:00', sensor1: 34.2, sensor2: 83.1 },
    { dateRecorded: '2018-03-31 10:05:00', sensor1: 65.2, sensor2: 44.5 }
]

I can get each sensors data returned individually but I can't seem to figure out how to group the outputs by the dateRecorded column and then put each sensor's data in it's own key/value pair on a single line?

That output JSON structure isn't valid JSON. -> try it on jsonlint.com — Raymond Nijland
– Raymond Nijland, Commented Apr 2, 2018 at 14:40

Raymond Nijland · Accepted Answer · 2018-04-02 15:23:47Z

2

You JSON output structure isn't valid JSON.

data: [
    { dateRecorded: '2018-03-31 10:00:00', sensor1: 34.2, sensor2: 83.1 },
    { dateRecorded: '2018-03-31 10:05:00', sensor1: 65.2, sensor2: 44.5 }
]

This one is a valid JSON structure so this is what i aim for in mine answer.

{
    "data": [{
        "dateRecorded": "2018-03-31 10:00:00",
        "sensor1": 34.2,
        "sensor2": 83.1
    }, {
        "dateRecorded": "2018-03-31 10:05:00",
        "sensor1": 65.2,
        "sensor2": 44.5
    }]
}

This is possible with pure MySQL only.

create table/insert data

CREATE TABLE Table1
    (`id` int, `sensorId` int, `dateRecorded`datetime, `tempF` double)
;

INSERT INTO Table1
    (`id`, `sensorId`, `dateRecorded`, `tempF`)
VALUES
    (1, 1, '2018-03-31 10:00:00', 34.2),
    (2, 2, '2018-03-31 10:00:00', 83.1),
    (3, 2, '2018-03-31 10:05:00', 44.5),
    (4, 1, '2018-03-31 10:05:00', 65.2)
;

To generate the inner JSON structure with MySQL like

{
        "dateRecorded": "2018-03-31 10:00:00",
        "sensor1": 34.2,
        "sensor2": 83.1
    }, {
        "dateRecorded": "2018-03-31 10:05:00",
        "sensor1": 65.2,
        "sensor2": 44.5
    }

You need to make use of the functions CONCAT and GROUP_CONCAT to generate the JSON strings.

Query

SELECT 
   CONCAT (
       '{'
     , '"dateRecorded": ', '"', Table1.dateRecorded, '"'
     , ','
     , GROUP_CONCAT(     
         CONCAT(
           '"sensor', Table1.sensorId, '":', Table1.tempF
         )
         ORDER BY
          Table1.id ASC
       )
     , '}'
   )
    AS json_data_records
FROM 
 Table1 
GROUP BY
 Table1.dateRecorded
ORDER BY 
 Table1.dateRecorded ASC

Result

|                                                     json_data_records |
|-----------------------------------------------------------------------|
| {"dateRecorded": "2018-03-31 10:00:00","sensor1":34.2,"sensor2":83.1} |
| {"dateRecorded": "2018-03-31 10:05:00","sensor2":44.5,"sensor1":65.2} |

see demo http://www.sqlfiddle.com/#!9/d6db452/6

p.s the order off sensor1 and sensor2 in the second record are reversed.
because off ORDER BY id ASC within the GROUP_CONCAT function.
Beside This order exists within the source data.

To generate the complete JSON like

{
    "data": [{
        "dateRecorded": "2018-03-31 10:00:00",
        "sensor1": 34.2,
        "sensor2": 83.1
    }, {
        "dateRecorded": "2018-03-31 10:05:00",
        "sensor1": 65.2,
        "sensor2": 44.5
    }]
}

We need the change the existing query so it's using CONCAT and GROUP_CONCAT to merge the last output.

Query

SELECT 
 CONCAT(
    '{'
      , '"data": ['
          , GROUP_CONCAT(json_records.json)
     , ']'
   , '}'
 )
  AS json
FROM ( 
  SELECT
     CONCAT (
         '{'
       , '"dateRecorded": ', '"', Table1.dateRecorded, '"'
       , ','
       , GROUP_CONCAT(     
           CONCAT(
             '"sensor', Table1.sensorId, '":', Table1.tempF
           )
           ORDER BY
            Table1.id ASC
         )
       , '}'
     ) 
      AS json
  FROM 
   Table1 
  GROUP BY
   Table1.dateRecorded
  ORDER BY 
   Table1.dateRecorded ASC
) 
 AS json_records

Result

|                                                                                                                                                    json |
|---------------------------------------------------------------------------------------------------------------------------------------------------------|
| {"data": [{"dateRecorded": "2018-03-31 10:00:00","sensor1":34.2,"sensor2":83.1},{"dateRecorded": "2018-03-31 10:05:00","sensor2":44.5,"sensor1":65.2}]} |

see demo http://www.sqlfiddle.com/#!9/d6db452/19

answered Apr 2, 2018 at 15:23

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jclifford90 Over a year ago

My apologies that my original post showed incorrect JSON structure, this is my first attempt with JSON. Your answer was exactly what I was looking for and I appreciate the depth to explain each section fully.

jclifford90 Over a year ago

So I have a bit of a follow-up question. Originally when my sql was just for one specific sensor, I was using this line to send to a Morris JS line chart <?php echo json_encode($rows);?> That would output something like this: [{"dateRecorded":"2018-04-02 00:50:13","sensor1":"67.33"},{"dateRecorded":"2018-04-02 01:00:15","sensor1":"67.33"}] But it seems your answer being that it already has the return JSON formatted, it comes back as an array of objects? How would I then output the results for the line chart to consume?

Raymond Nijland Over a year ago

@jclifford90 i don't know Morris Javascript line chart so you need to ask a new question with the javascript tag.

Collectives™ on Stack Overflow

Querying MySQL Table to Return JSON

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related