Use message value to build JSONata expression

Hi All,

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

image
This is the Array I'm using

image
How I want the data (ID specified directly not using variable)

Any help would be greatly appreciated!

$$.payload.*.ID would give you 1619

Thanks, I have just tried it but it doesn't seem to be pulling the value out.


There's no topic now

This is the object i used it is not the object you are showing now.

If 1619 is the only property of payload you could use $keys($$.payload) to

Also best to use copy value buttons in the debug pane, and paste the objects then users can copy and test.

Thanks agian,

{"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"},"parts":{"id":"dd22a940791b2089","type":"array","count":1,"len":1,"index":0},"_msgid":"55126eb433c501f9","ID":"ID"}

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.

Hopefully this makes sense.

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.

DOH!

that first screenshot was wrong, (the one quoted)

Although is ultimately how it needs to be formatted

This is the data as is before the change block.

{"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.

so you want to get "ID" not the value 1619?

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".

I think that's it I'll return the primary key AS id and reference that. Thanks again for your help!