# Use message value to build JSONata expression

**URL:** <https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177>\
**Category:** General\
**Created:** [3 November 2021 16:28 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177 "2021-11-03T16:28:55Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![B14702](https://avatars.discourse-cdn.com/v4/letter/b/cc9497/32.png) [@B14702](https://discourse.nodered.org/u/B14702)\
**Post date:** [3 November 2021 16:28 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/1 "2021-11-03T16:28:55Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/6/56b61af739ec4c020266ec87af7bb71fd692017b.png)  
This is the Array I'm using

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/a/da7c698029b62fccf06a2aeef4edf11fe5ac5d69.png)  
How I want the data (ID specified directly not using variable)

Any help would be greatly appreciated!

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 November 2021 16:34 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/2 "2021-11-03T16:34:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![B14702](https://avatars.discourse-cdn.com/v4/letter/b/cc9497/32.png) [@B14702](https://discourse.nodered.org/u/B14702)\
**Post date:** [3 November 2021 16:39 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/3 "2021-11-03T16:39:55Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/f/4f66c8d4a8b6e1e24f52e64a43524e2847d8a285.png)  
There's no topic now

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 November 2021 16:41 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/4 "2021-11-03T16:41:51Z")

</div>

> [@B14702](#):
>
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/6/56b61af739ec4c020266ec87af7bb71fd692017b.png)  
> This is the Array I'm using

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.

---

<div class="post-metadata">

**Author:** ![B14702](https://avatars.discourse-cdn.com/v4/letter/b/cc9497/32.png) [@B14702](https://discourse.nodered.org/u/B14702)\
**Post date:** [3 November 2021 16:46 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/5 "2021-11-03T16:46:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 November 2021 16:48 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/6 "2021-11-03T16:48:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![B14702](https://avatars.discourse-cdn.com/v4/letter/b/cc9497/32.png) [@B14702](https://discourse.nodered.org/u/B14702)\
**Post date:** [3 November 2021 16:49 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/7 "2021-11-03T16:49:30Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/9/691ba06d48dd9a698c366b09a3f7c7cdd1afd547.png)

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

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 November 2021 16:53 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/8 "2021-11-03T16:53:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![B14702](https://avatars.discourse-cdn.com/v4/letter/b/cc9497/32.png) [@B14702](https://discourse.nodered.org/u/B14702)\
**Post date:** [3 November 2021 16:58 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/9 "2021-11-03T16:58:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 November 2021 17:04 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/10 "2021-11-03T17:04:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![B14702](https://avatars.discourse-cdn.com/v4/letter/b/cc9497/32.png) [@B14702](https://discourse.nodered.org/u/B14702)\
**Post date:** [3 November 2021 18:34 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/11 "2021-11-03T18:34:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [2 January 2022 18:34 UTC](https://discourse.nodered.org/t/use-message-value-to-build-jsonata-expression/53177/12 "2022-01-02T18:34:33Z")

</div>

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