# Help needed with JSONata syntax for replacement of key field

**URL:** <https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068>\
**Category:** General\
**Created:** [13 January 2021 15:51 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068 "2021-01-13T15:51:19Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![SilBlkPanther](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/silblkpanther/32/35314_2.png) [@SilBlkPanther](https://discourse.nodered.org/u/SilBlkPanther)\
**Post date:** [13 January 2021 15:51 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/1 "2021-01-13T15:51:19Z")

</div>

First post, so hi to everybody 🙂

I'm trying to display a mysql result to a chart and for this using JSONata in a change note to rename the key fields of the resulting array to X/Y. Unfortunately one field of my sql return set is named **MIN(timeStamp)** and I'm not able to find the right syntax for replacing it. If I only have **timeStamp** as key it works. Below screenshots should help to understand my situation:

Working (w/o MIN in key field of the array):

 ![W](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/7/e79c3a0f8e82ead7d83cc93a0b6a15733a111cbe.jpeg)

Not working (w MIN in key field of the array):

 ![NW](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/5/d54386957b51eefd7a9f96e85663072266b6772b.jpeg)

If I change line 5 to ` "x": MIN(timeStamp),` I get an **Attempted to invoke a non-function error.**

Is there an easy way to archive my goal?

Thanks

---

<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:** [13 January 2021 15:56 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/2 "2021-01-13T15:56:45Z")

</div>

Try putting a $ eg. $min().

You should also be able to do all this in the mysql request, including changing key names.

---

<div class="post-metadata">

**Author:** ![SilBlkPanther](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/silblkpanther/32/35314_2.png) [@SilBlkPanther](https://discourse.nodered.org/u/SilBlkPanther)\
**Post date:** [13 January 2021 16:43 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/3 "2021-01-13T16:43:17Z")

</div>

$min(..) is not helping / doing anything.  
No error but no x values in the resulting array.

The sql stmt was:

```auto
SELECT MIN(timeStamp), gasMeterValueVerified FROM gasMeter where gasMeterValueVerified is not null AND DATE(timeStamp) > DATE(NOW() - INTERVAL 1 DAY) GROUP BY gasMeterValueVerified

```

Your hint with changing the key name in sql was spot on, thanks.  
The sql statement is now:

```auto
SELECT MIN(timeStamp) as tStamp, gasMeterValueVerified FROM gasMeter where gasMeterValueVerified is not null AND DATE(timeStamp) > DATE(NOW() - INTERVAL 1 DAY) GROUP BY gasMeterValueVerified

```

With that line 5 is now:

```auto
"x": tStamp, 

```

My initial problem is now solved (in a different way as expected 🙂 ).  
Still if anyone would know how to do the replacement in JSON I would be interested in the same.

---

<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:** [13 January 2021 16:59 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/4 "2021-01-13T16:59:26Z")

</div>

Transform

```auto
[{"id":"5fa7772f.5a4538","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"x\":\"2021-01-07T21:24:44\",\"y\":5},{\"x\":\"2021-01-07T21:24:54\",\"y\":4},{\"x\":\"2021-01-07T21:24:59\",\"y\":2}]","payloadType":"json","x":180,"y":3920,"wires":[["62056daf.b0ef74"]]},{"id":"62056daf.b0ef74","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload ~> |[*]|{'newx':x,'newy':y},[\"x\",\"y\"]|","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":3920,"wires":[["3a43c3c5.81a3b4"]]},{"id":"3a43c3c5.81a3b4","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":620,"y":3920,"wires":[]}]

```

> **[Other Operators · JSONata](https://docs.jsonata.org/other-operators#-------transform)**
>
> \## \`&\` (Concatenation)

[edit] Or

```auto
payload.{'newx':x,'newyy':y}

```

but in you sql why not  
`SELECT MIN(timeStamp) as x, gasMeterValueVerified as y`

---

<div class="post-metadata">

**Author:** ![SilBlkPanther](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/silblkpanther/32/35314_2.png) [@SilBlkPanther](https://discourse.nodered.org/u/SilBlkPanther)\
**Post date:** [13 January 2021 22:20 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/5 "2021-01-13T22:20:08Z")

</div>

Had not thought of naming both results right away in sql to x and y thou I still need to transfer into the **series, label, data** array for the chart. Appreciate the new view thou 🙂

What I meant with solution in JSON was to get the transformation with the key name **MIN(timeStamp)** to x so that JSON is not interpreting it as a function but taking the key name as such for the replacement. As I have a solution this is has now low priority but would be interesting if there is a way.

---

<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:** [13 January 2021 22:28 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/6 "2021-01-13T22:28:07Z")

</div>

When i first read your post i confused MIN() the sql, with $min() j: .  
Try backticks `MIN()`

```auto
[{"id":"62056daf.b0ef74","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.{'newx':`MIN(timeStamp)`,'newyy':y}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":3920,"wires":[["3a43c3c5.81a3b4"]]},{"id":"5fa7772f.5a4538","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"MIN(timeStamp)\":\"2021-01-07T21:24:44\",\"y\":5},{\"MIN(timeStamp)\":\"2021-01-07T21:24:54\",\"y\":4},{\"MIN(timeStamp)\":\"2021-01-07T21:24:59\",\"y\":2}]","payloadType":"json","x":180,"y":3920,"wires":[["62056daf.b0ef74"]]},{"id":"3a43c3c5.81a3b4","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":620,"y":3920,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![SilBlkPanther](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/silblkpanther/32/35314_2.png) [@SilBlkPanther](https://discourse.nodered.org/u/SilBlkPanther)\
**Post date:** [13 January 2021 22:39 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/7 "2021-01-13T22:39:09Z")

</div>

That did the trick, thanks  
Will now rather work with your sql solution but good to know the other way 🙂

---

<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:** [27 January 2021 22:39 UTC](https://discourse.nodered.org/t/help-needed-with-jsonata-syntax-for-replacement-of-key-field/39068/8 "2021-01-27T22:39:19Z")

</div>

Closed on 27 January 2021 22:39 UTC

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