28857

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 | +----+----------+---------------------+-------+

<br />

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 } ]

<br /> 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?

Answer1:

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. <br />

<strong>create table/insert data</strong>

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.

<strong>Query</strong>

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

<strong>Result</strong>

| 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 <a href="http://www.sqlfiddle.com/#!9/d6db452/6" rel="nofollow">http://www.sqlfiddle.com/#!9/d6db452/6</a>

p.s the order off sensor1 and sensor2 in the second record are reversed. <br /> because off ORDER BY id ASC within the GROUP_CONCAT function. <br /> 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.

<strong>Query</strong>

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

<strong>Result</strong>

| 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 <a href="http://www.sqlfiddle.com/#!9/d6db452/19" rel="nofollow">http://www.sqlfiddle.com/#!9/d6db452/19</a>

Recommend

  • JavaScript Retain certain properties and remove other properties from array of objects
  • Unix: what modifier key does “M-” refer to (e.g. M-C)
  • Python File handling: Seaching for specific numbers
  • Move an array element to a new index in PHP
  • Set UITableView BackgroundColor Universally with UIAppearance
  • OpenXML how to get cell in range
  • How do I calculate a grouped z score in R using dplyr?
  • getElementsByClassName and AJAX
  • wpf: update multiple controls via dispatcher
  • Ruby: Why does this way of using map throw an error?
  • Calling java project from Mathematica
  • PHP file_exists() anomaly
  • Parse Framework with Swift
  • How to use the resource module to measure the running time of a function?
  • How to open multiple instances of a program in Linux
  • TSQL Rolling Average of Time Groupings
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • cell spacing in div table
  • XSLT foreach repeating nodes to flat
  • How to create a 2D image by rotating 1D vector of numbers around its center element?
  • Find group of records that match multiple values
  • Ubuntu and bcrypt
  • Pycharm: Marking a folder as 'sources root' is not recursive for subfolders
  • Thread 1: EXC_BAD_ACCESS (code =1 address = 0x0)
  • Using JRuby with Rails 3.2
  • Unable to install Git-core+svn by MacPorts
  • Groovy: Unexpected token “:”
  • Caching attributes in superclass
  • Replace value with Factor in r data.table
  • Is it possible to access block's scope in method?
  • SignalR .NET Client Invoke throws an exception
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Read text file and split every line in MSBuild
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • WPF Applying a trigger on binding failure
  • embed rChart in Markdown
  • Unable to use reactive element in my shiny app