Beginner needs some json to mysql support

itchy2 · 15 February 2019 21:55

Hi all,

I'm totally new to nodered. I watched some videos and red some documentation but now it's time to get practial.

My "Idea": I have a Webapi on a device where I can get data in Json format:

{  
   "UniqueId":"Meter#1",
   "Timestamp":"2019-02-15T22:33:09.622867513+01:00",
   "Unix":1550266389,
   "ModbusDeviceId":1,
   "Power":{  
      "L1":0,
      "L2":58.04953384399414,
      "L3":7.187046051025391
   },
   "Voltage":{  
      "L1":235.2692108154297,
      "L2":234.9277801513672,
      "L3":233.99427795410156
   },
   "Current":{  
      "L1":0.06178390234708786,
      "L2":0.46165990829467773,
      "L3":0.08737564086914062
   },
   "Cosphi":{  
      "L1":0.12718620896339417,
      "L2":0.5964568853378296,
      "L3":0.33428090810775757
   },
   "Import":{  
      "L1":26.285999298095703,
      "L2":43.43699645996094,
      "L3":40.39099884033203
   },
   "TotalImport":110.11399841308594,
   "Export":{  
      "L1":0,
      "L2":0,
      "L3":0.0009999999310821295
   },
   "TotalExport":0.0009999999310821295,
   "THD":{  
      "VoltageNeutral":{  
         "L1":3.460484504699707,
         "L2":3.1392886638641357,
         "L3":3.341616153717041
      },
      "AvgVoltageNeutral":3.322300434112549
   },
   "Frequency":49.960819244384766
}

This Json shall be written to a mysql database.

To provide a feeling for my data: The Json-Data is from an electrical power meter; accessed via Modbus. This data shall be written into my mysql database to be presented in "volkszaehler" GUI.

At the moment I'm a stuck at the point where to prepare the data from the json api.

I downloaded the flow "contrib-json" to access all single pathes in the JSON but this cannot be the solution. Does somebody of you know a flow which can access all values of a json in an array or something like that? I expect, having it all separated, will lead to a challenge on inserting them into the database.

My current status:

[
    {
        "id": "f493ad5d.f5649",
        "type": "tab",
        "label": "Flow 4",
        "disabled": false,
        "info": ""
    },
    {
        "id": "943726f5.867c68",
        "type": "debug",
        "z": "f493ad5d.f5649",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": true,
        "tostatus": true,
        "complete": "payload",
        "x": 530,
        "y": 297,
        "wires": []
    },
    {
        "id": "f5b6def7.5a7d4",
        "type": "http request",
        "z": "f493ad5d.f5649",
        "name": "",
        "method": "GET",
        "ret": "txt",
        "url": "http://192.168.178.90:8080/last/1",
        "tls": "",
        "x": 292.5,
        "y": 280,
        "wires": [
            [
                "943726f5.867c68",
                "8824b733.35f7d8"
            ]
        ]
    },
    {
        "id": "e4cd6f0a.b4827",
        "type": "inject",
        "z": "f493ad5d.f5649",
        "name": "",
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "repeat": "",
        "crontab": "",
        "once": true,
        "onceDelay": "0.5",
        "x": 104,
        "y": 279,
        "wires": [
            [
                "f5b6def7.5a7d4"
            ]
        ]
    },
    {
        "id": "8824b733.35f7d8",
        "type": "json",
        "z": "f493ad5d.f5649",
        "name": "",
        "property": "payload",
        "action": "obj",
        "pretty": false,
        "x": 489.5,
        "y": 390,
        "wires": [
            [
                "2ce6ca80.f16306",
                "a35db477.ed44e8",
                "d3ca88cb.376278",
                "607ff54d.ef5f8c",
                "fc55729f.c4584",
                "d99a608b.87b88",
                "3371022e.4894ce"
            ]
        ]
    },
    {
        "id": "2ce6ca80.f16306",
        "type": "debug",
        "z": "f493ad5d.f5649",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "x": 719.5,
        "y": 405,
        "wires": []
    },
    {
        "id": "a35db477.ed44e8",
        "type": "contrib-json",
        "z": "f493ad5d.f5649",
        "engine": "JSONPath",
        "command": "jq",
        "expr": "$.UniqueId",
        "complete": "property",
        "prop": "payload",
        "name": "",
        "x": 575.5,
        "y": 593,
        "wires": [
            [
                "1512f11b.4c817f"
            ]
        ]
    },
    {
        "id": "1512f11b.4c817f",
        "type": "debug",
        "z": "f493ad5d.f5649",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "x": 765.5,
        "y": 583,
        "wires": []
    },
    {
        "id": "d3ca88cb.376278",
        "type": "contrib-json",
        "z": "f493ad5d.f5649",
        "engine": "JSONPath",
        "command": "jq",
        "expr": "$.Timestamp",
        "complete": "property",
        "prop": "payload",
        "name": "",
        "x": 575.5,
        "y": 631,
        "wires": [
            [
                "1512f11b.4c817f"
            ]
        ]
    },
    {
        "id": "607ff54d.ef5f8c",
        "type": "contrib-json",
        "z": "f493ad5d.f5649",
        "engine": "JSONPath",
        "command": "jq",
        "expr": "$.Unix",
        "complete": "property",
        "prop": "payload",
        "name": "",
        "x": 558.5,
        "y": 675,
        "wires": [
            [
                "1512f11b.4c817f"
            ]
        ]
    },
    {
        "id": "fc55729f.c4584",
        "type": "contrib-json",
        "z": "f493ad5d.f5649",
        "engine": "JSONPath",
        "command": "jq",
        "expr": "$.ModbusDeviceId",
        "complete": "property",
        "prop": "payload",
        "name": "",
        "x": 567.5,
        "y": 722,
        "wires": [
            [
                "1512f11b.4c817f"
            ]
        ]
    },
    {
        "id": "d99a608b.87b88",
        "type": "contrib-json",
        "z": "f493ad5d.f5649",
        "engine": "JSONPath",
        "command": "jq",
        "expr": "$.Power",
        "complete": "property",
        "prop": "payload",
        "name": "",
        "x": 567.5,
        "y": 785,
        "wires": [
            [
                "1512f11b.4c817f"
            ]
        ]
    },
    {
        "id": "3371022e.4894ce",
        "type": "contrib-json",
        "z": "f493ad5d.f5649",
        "engine": "JSONPath",
        "command": "jq",
        "expr": "$.Power.L1",
        "complete": "property",
        "prop": "payload",
        "name": "",
        "x": 567.5,
        "y": 830,
        "wires": [
            [
                "1512f11b.4c817f"
            ]
        ]
    }
]

Thx and KR
Itchy2

knolleary · 15 February 2019 22:02

Hi,

there's absolutely no need to install any extra nodes to work with data like this - everything you need is already in the default set of nodes.

It is worth getting a little familiar with JavaScript as that will help you understand how to start working with the data. You can then use a Function node to work with the data directly. We also have JSONata in the Change node that lets you do transformations of the data - another option in the toolbox.

I would suggest you decide what you want the individual rows of data to be in the database. That then gives you something to work towards.

itchy2 · 16 February 2019 00:36

Hi knolleary,

thanks for your response. Good to hear that everything is working based on the default flows. So it's just a topic of the right combination

At the moment I have no expieriences with JavaScript.

I my standard flows I cannot find JSONata.

I'm not quite sure if I understood your last point corrent but, I want the single lines to be inserted to the database.

Thx and KR
Itchy2

knolleary · 16 February 2019 00:45

Have you decided what columns you will have in your database table and how the values in that JSON string will map to them?

itchy2 · 16 February 2019 00:53

Sorry, now I understood your question. I want to stick as close as possible to json.

Table:

UniqueId   Timestamp   Unix   ModbusDeviceId   Power_L1   Power_L2 Power_L3   Voltage_L1   Voltage_L2   Voltage_L3   Current_L1   Current_L2   Current_L3   Cosphi_L1   Cosphi_L2   Cosphi_L3   Import_L1   Import_L2   Import_L3   TotalImport   Export_L1   Export_L2   Export_L3   TotalExport   THD_L1   THD_L2   THD_L3   THD_AVG   Freq

The JSON structure can be mapped based on the attributes name.

KR
Itchy2

knolleary · 16 February 2019 01:04

If you are using node-red-node-mysql to connect to your database, then that node expects you to pass it a message with its topic property set to the SQL query you want to run - in this case, an INSERT statement.

So you could use a Template node to generate that query. Configure the node to set msg.topic rather than its default of msg.payload. Then set the template to:

INSERT INTO  myTable (
   UniqueId,
   Timestamp,
   Unix,
   ModbusDeviceId,
   Power_L1,
   ...you can type the rest out...)
VALUES( 
   '{{payload.UniqueId}}',
   '{{payload.Timestamp}}',
   {{payload.Unix}},
   {{payload.ModbusDeviceId}},
   {{payload.Power.L1}},
   {{payload.Power.L2}},
   ... etc etc etc
)

The Template node uses the mustache template syntax to insert message properties where they are identified like {{this}}.

If you pass the output from that to a Debug node before you think about mysql, you can check the query looks right.

itchy2 · 16 February 2019 21:56

Unbelievable. It can be so easy. Thanks a lot.

At the moment i'm struggeling at one point with the date conversion. I don't know if node red has functions to do type conversions. I tried it with SQL but it didn't work:

INSERT INTO gosdm630.input_data ( UniqueDeviceId, 
Timestamp, 
Unix, 
ModbusDeviceId, 
Power_L1, 
Power_L2, 
Power_L3, 
Voltage_L1, 
Voltage_L2, 
Voltage_L3, 
Current_L1, 
Current_L2, 
Current_L3, 
Cosphi_L1, 
Cosphi_L2, 
Cosphi_L3, 
Import_L1, 
Import_L2, 
Import_L3, 
TotalImport, 
Export_L1, 
Export_L2,
Export_L3,
TotalExport,
THD_L1,
THD_L2,
THD_L3,
THD_AVG,
Freq ) 
select
'Meter#1',
convert("2019-02-16T22:22:43.351393215+01:00",DATETIME),
1550352163,
1,
0,
14.74150276184082,
90.65894317626953,
235.2493438720703,
236.3384552001953,
233.36117553710938,
0.059153586626052856,
0.3269297182559967,
0.601137638092041,
0.1323450654745102,
0.19732831418514252,
0.6496763825416565,
26.285999298095703,
44.321998596191406,
41.14099884033203,
111.74899291992188,
0,
0,
0.0009999999310821295,

0.0009999999310821295,
3.0032758712768555,
2.426023483276367,
2.7081596851348877,
2.808605432510376,
49.95524978637695 ;

INSERT INTO  gosdm630.input_data (
   UniqueDeviceId,
   Timestamp,
   Unix,
   ModbusDeviceId,
   Power_L1,
   Power_L2,
   Power_L3,
   Voltage_L1,
   Voltage_L2,
   Voltage_L3,
   Current_L1,
   Current_L2,
   Current_L3,
   Cosphi_L1,
   Cosphi_L2,
   Cosphi_L3,
   Import_L1,
   Import_L2,
   Import_L3,
   TotalImport,
   Export_L1,
   Export_L2,
   Export_L3,
   TotalExport,
   THD_L1,
   THD_L2,
   THD_L3,
   THD_AVG,
   Freq
)
VALUES( 
   '{{payload.UniqueId}}',
   convert("{{payload.Timestamp}}",DATETIME),
   {{payload.Unix}},
   {{payload.ModbusDeviceId}},
   {{payload.Power.L1}},
   {{payload.Power.L2}},
   {{payload.Power.L3}},
   {{payload.Voltage.L1}},
   {{payload.Voltage.L2}},
   {{payload.Voltage.L3}},
   {{payload.Current.L1}},
   {{payload.Current.L2}},
   {{payload.Current.L3}},
   {{payload.Cosphi.L1}},
   {{payload.Cosphi.L2}},
   {{payload.Cosphi.L3}},
   {{payload.Import.L1}},
   {{payload.Import.L2}},
   {{payload.Import.L3}},
   {{payload.TotalImport}},
   {{payload.Export.L1}},
   {{payload.Export.L2}},
   {{payload.Export.L3}},
   {{payload.TotalExport}},
   {{payload.THD.VoltageNeutral.L1}},
   {{payload.THD.VoltageNeutral.L2}},
   {{payload.THD.VoltageNeutral.L3}},
   {{payload.THD.AvgVoltageNeutral}},
   {{payload.Frequency}}   
   
)

It's working if i insert the data to the string field in the database, but not into a datetime.

Thx and KR
Itchy2

Topic		Replies	Views
Basic advice needed on parsing JSON General	3	700	14 April 2019
Newbie fails with json formula General	2	341	10 October 2019
Post to sql from array General	16	2251	7 October 2019
NodeRed Json to Influxdb General	16	5278	1 June 2020
Problem getting data from mysql db to line chart General	6	235	4 October 2022

Beginner needs some json to mysql support

Related Topics