Remove header from gps object data


I receive data from a gps and want to write it to a sql database. How do i get rid of the longitude, latitude, altitude, accuracy, provider etc in the object data. I only want to write the actual values to sql.

The following is the data i get from the gps:


latitude: -34.1304

longitude: 22.09785

altitude: 110.5

accuracy: 13.399999618530273

vertical_accuracy: 0

bearing: 0

speed: 0

elapsedMs: 6

provider: "gps"

All you need is an INSERT statement which lists the fields you want and the values to put in them.
But I'm sure from looking at your previous posts that you already know this, so can you explain why this is not applicable?

For example:

You didn't give enough detail of the input data to do more than guess at where latitude and longitude might be found. An actual example of the data (not a screen capture) would help.

:This is how i receive the data from the gps. I want to get rid of the longitude:, latitude: Altitude, etc and only send the actual data to sql.


In a chnage node
set msg.payload
to JSONata J: $$.payload.*

This will return an array of values
[-34.130000, 22.09785, -32.4, etc....]

Many thanks that works

The following is the data that i receive now. How to i do a insert query into sql?


The example @jbudd gave explains how to do it (ie: there was no reason to remove the property names as the database has columns where you need to inject the data to - depending on the database setup).

Hi @jbud

How should i send you the data?

Are you really still having difficulty?

The first example I gave shows how you can construct an SQL INSERT statement.
So let's break it down:

msg.topic is INSERT INTO test (latitude, longitude) VALUES (?, ?)
The db table is called "test", it has two fields "latitude" and "longitude".
Two fields to insert, so two qestion mark place holders in the VALUES part.

msg.payload is [msg.payload.latitude, msg.payload.longitude].
It is an array of values which will be inserted by the mysql node in place of the question marks.
Two database fields, two question marks, two elements in the array.
Note that msg.payload.latitude will be the value -34.13034... It will not include the word "latitude"

My example works with the Mariadb node.
I don't know which SQL database you have, so I have no idea if this is the syntax you need.
You can probably find out by searching the web or just by trying it.

I really think you have enough information to solve this yourself but for future reference:
If you use a Debug node you can show msg.payload in the right hand column.
There are two buttons which let you copy the element path eg "msg.payload" or the value. Copy the value.

In the forum editor click on the preformatted text button
Untitled 3

And paste the copied data over the top of the highlighted text
Untitled 6

1 Like

Many thanks for all the help.

This should get me going :+1:

Many thanks guys. I managed to get it working.

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