Long time fan first time poster, love the project fantastic piece of kit.
I've run into a problem that I can't get past despite dredging through the forums.
I'm trying to put data from multiple sql tables into firebase, the majority of the process is fine but I'm trying to get clever with the Primary Key. I get an array of data from the table and format it to firebases liking.
I need to set whatever the primary key field (ID) is to the topic.
My problem is the name of the Primary key changes depending on the table i'm querying, I can get what each one is called. Where i'm stuck is trying to use the change node and JSONata to do it.
I can't figure out how to use the primary key name to build the path. it comes in as msg.id (for example) now I would expect if I put the following it would work.
Set msg.topic
payload.[$$.id]
However I'm actually returned the value of msg.id not msg.payload.id
This is the Array I'm using
How I want the data (ID specified directly not using variable)
That's the array, maybe I need to explain better. The value that is in ID: "ID" is the name of the primary key. I need that to know which part of the array to use for the topic. As it's from mysql it could be for example act_id or this_id so I need to feed that value into the JSON expression. This will allow me to pull the ID from the array to set the topic correctly.
With that object it would be $$.payload.ID
There is also a copy path button that appears to the right of vars in the debug pane.
P.s. place all code between backticks
```
code
```
use thr </. button to produce the backticks.
{"payload":{"ID":1619,"date":"2021-07-23T15:02:34.000Z","vid":31318492,"data":"{"lng":1.666267,"heading":178,"lat":51.416027,"rgOpState":0}","RGtime":"2021-07-23T16:01:06.792Z","text":"rgLocUpdate"},"topic":"SELECT * FROM Events limit 1","parts":{"id":"17ca476715968dd6","type":"array","count":1,"len":1,"index":0},"_msgid":"6bfcd59b5a892eb6"}
I'm are you can copy the paths but the path here depends on what the primary key is it could be.
payload.ID but in a different table it could be payload.this_ID
So I get the primary key and set it to msg.id and I then want to filter the array with that so msg.payload.(msg.id value) so it copes with the changes in the object key in the array.
I'm trying to reference the path but that will change depending on whats in the array.
Lets say ID in the array changes to This_ID as we are looking at a different table, I know that This_ID is the primary key as I can query the DB to check it and it comes in as msg.id. I then need to set the msg.topic to This_ID in the array.
So in the above object "ID" is what you are looking for?
Would it be easier to set the sql query to return the primary key as a set name.
what is the query to retrieve the array?
You can get a list of keys in the payload, problem being the object order is not a guarantee.
You could try something like $keys($$.payload)["ID" = $substring($,-2)] which should work as long as the primary key ends "ID" and no other property ends "ID".
