How to transpose data of a json file into a sql table

General
1

I'm working on a mysql database for measurements. The data of the measurements are saved as a csv file, which I managed to be converted into json. Now my problem is that I want only the last rows of the csv to be transposed into two additional columns 'characteristics' and 'value'.

{ "Date": "08.03.2019", "machine": "G2", "ordernr": 1234567890, "TTNR": 311436, "L_S1": "36,92", "L_S2": "23,9", "L_KS1": "7,8", "L_KS1": "6,6", }

the expected table has the column names 'date', 'machine', 'ordernr', 'characteristics' and 'value'

the characteristics should be L_S1, L_S2, L_KS1, L_KS2 and the value: 36.92, 23.9, 7.8, 6.6

2

When you create the INSERT statement you specify the that you want to insert data into a column and where that bit of data is in the msg object.

The order it is within there doesn’t matter

3

so if i have 20 different characteristics, do have to right them all in my function node down ?

insertIntoTemplate += ''' + msg.payload['L_S1','L_S2','L_KS1','L_KS2'] + ''';

4

If you want them in your database - yes.

1 Like