Insert data in to MSSQL from JSON

General
1

I have a json string:

{"sensorDatas":[{"flag":"SIM","str":"89860442192090055870"},{"flag":"Ua","value":235.4},{"flag":"Ub","value":0.0},{"flag":"Uc","value":0.0},{"flag":"Uab","value":235.1},{"flag":"Ubc","value":0.0},{"flag":"Uca","value":235.1},{"flag":"Ia","value":0.000},{"flag":"Ib","value":0.000},{"flag":"Ic","value":0.000},{"flag":"Pt","value":0.000},{"flag":"Qt","value":0.000},{"flag":"St","value":0.000},{"flag":"COS","value":0.000},{"flag":"Ep","value":0.000},{"flag":"En","value":0.000},{"flag":"Eq","value":0.000},{"flag":"Vper","value":100},{"flag":"LPer","value":87},{"flag":"CHAT","value":3.8},{"flag":"CHBT","value":0.0},{"flag":"CHCT","value":0.0},{"flag":"VHAT","value":0.0},{"flag":"VHBT","value":0.0},{"flag":"VHCT","value":0.0},{"flag":"Pta","value":0.000},{"flag":"Ptb","value":0.000},{"flag":"Ptc","value":0.000},{"flag":"F","value":49.970},{"flag":"Enow","value":0.000},{"flag":"DMD","value":0.000},{"flag":"dBm","value":25},{"flag":"TMP1","value":0.0},{"flag":"TMP2","value":0.0},{"flag":"TMP3","value":0.0},{"flag":"TMP4","value":0.0},{"flag":"DI","switcher":0},{"flag":"DO1","switcher":0},{"flag":"DO2","switcher":0}]}

I need to use mssql to insert into my DB as 1 record these sensorDatas.
Please help me!
Thank you!

2

Where has the data come from that gives you a string in that format?

3

I get from my energy meter used MQTT protocol

4

Ok, start by configuring the mqtt node to parse the JSON and give you a JavaScript object.

I would then use a change node to Move msg.payload.sensorDatas To msg.payload. what you do then depends on the structure of the database tables, which you have not told us

5

Oh. thank you. My table structure:

[SIM] varchar(25),
[Ua] decimal(9, 4),
[Ub] decimal(9, 4),
[Uc] decimal(9, 4),
[Uab] decimal(9, 4),
[Ubc] decimal(9, 4),
[Uca] decimal(9, 4),
[F] decimal(9, 4),
[Ia] decimal(9, 4),
[Ib] decimal(9, 4),
[Ic] decimal(9, 4),
[Iavg] decimal(9, 4),
[Pt] decimal(9, 4),
[Pa] decimal(9, 4),
[Pb] decimal(9, 4),
[Pc] decimal(9, 4),
[Qt] decimal(9, 4),
[Qa] decimal(9, 4),
[Qb] decimal(9, 4),
[Qc] decimal(9, 4),
[St] decimal(9, 4),
[Sa] decimal(9, 4),
[Sb] decimal(9, 4),
[Sc] decimal(9, 4),
[COS] decimal(6, 5),
[EPt] decimal(9, 3),
[EPa] decimal(9, 3),
[EPb] decimal(9, 3),
[EPc] decimal(9, 3),
[EQt] decimal(9, 3),
[EQa] decimal(9, 3),
[EQb] decimal(9, 3),
[EQc] decimal(9, 3),
[En] decimal(9, 3),
[Enow] decimal(9, 3),
[LPer] decimal(9, 3),
[Vper] decimal(9, 3),
[CHAT] decimal(9, 3),
[CHBT] decimal(9, 3),
[CHCT] decimal(9, 3),
[VHAT] decimal(9, 3),
[VHBT] decimal(9, 3),
[VHCT] decimal(9, 3),
[DMD] decimal(9, 3),
[dBm] decimal(9, 3),
[TMP1] decimal(9, 3),
[TMP2] decimal(9, 3),
[TMP3] decimal(9, 3),
[TMP4] decimal(9, 3),
[DI] int,
[DO1] int,
[DO2] int

6

Next work out the details of the data you need to feed to the db node.

7

Can you help me. I tried many times but not success!

8

Tried what? Tried to work out what format you need for the data?
To do that you need first decide what query you want to run.

9

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.