# Beginner needs some json to mysql support

**URL:** <https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030>\
**Category:** General\
**Created:** [15 February 2019 21:55 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030 "2019-02-15T21:55:23Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![itchy2](https://avatars.discourse-cdn.com/v4/letter/i/8edcca/32.png) [@itchy2](https://discourse.nodered.org/u/itchy2)\
**Post date:** [15 February 2019 21:55 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030/1 "2019-02-15T21:55:23Z")

</div>

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:

```auto
{  
   "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:

```auto
[
    {
        "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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [15 February 2019 22:02 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030/2 "2019-02-15T22:02:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![itchy2](https://avatars.discourse-cdn.com/v4/letter/i/8edcca/32.png) [@itchy2](https://discourse.nodered.org/u/itchy2)\
**Post date:** [16 February 2019 00:36 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030/3 "2019-02-16T00:36:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [16 February 2019 00:45 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030/4 "2019-02-16T00:45:46Z")

</div>

> [@itchy2](#):
>
> I'm not quite sure if I understood your last point corrent but, I want the single lines to be inserted to the database.

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

---

<div class="post-metadata">

**Author:** ![itchy2](https://avatars.discourse-cdn.com/v4/letter/i/8edcca/32.png) [@itchy2](https://discourse.nodered.org/u/itchy2)\
**Post date:** [16 February 2019 00:53 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030/5 "2019-02-16T00:53:48Z")

</div>

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

Table:

```auto
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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [16 February 2019 01:04 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030/6 "2019-02-16T01:04:27Z")

</div>

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:

```sql
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.

---

<div class="post-metadata">

**Author:** ![itchy2](https://avatars.discourse-cdn.com/v4/letter/i/8edcca/32.png) [@itchy2](https://discourse.nodered.org/u/itchy2)\
**Post date:** [16 February 2019 21:56 UTC](https://discourse.nodered.org/t/beginner-needs-some-json-to-mysql-support/8030/7 "2019-02-16T21:56:56Z")

</div>

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:

```auto
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 ;

```

```auto
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