# Sunday JSONata Challenge!
**URL:** https://discourse.nodered.org/t/sunday-jsonata-challenge/21793
**Category:** General
**Created:** [16 February 2020 13:43 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793 "2020-02-16T13:43:57Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [16 February 2020 13:43 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/1 "2020-02-16T13:43:57Z")
Here's a bit of a fun challenge for anyone wanting to improve their JSONata Fu.
Take the output from the following nmap command output (change the IP address/subnet to match your own):
```auto
sudo nmap -sn --oX nmap.xml --privileged -R --system-dns --webxml 192.168.1.0/24
```
Then in Node-RED, read in the resulting XML file (`nmap.xml`) and push through the XML node to turn it into JSON.
You will note that the conversion wraps XML attributes in `$` properties that, in this case, are entirely unnecessary.
```json
...
{
"status": [
{
"$": {
"state": "up",
"reason": "arp-response",
"reason_ttl": "0"
}
}
],
"address": [
{
"$": {
"addr": "192.168.1.167",
"addrtype": "ipv4"
}
},
{
"$": {
"addr": "B8:27:EB:DF:49:7E",
"addrtype": "mac",
"vendor": "Raspberry Pi Foundation"
}
}
],
"hostnames": [
{
"hostname": [
{
"$": {
"name": "pi2.knightnet.co.uk",
"type": "PTR"
}
}
]
}
],
"times": [
{
"$": {
"srtt": "406",
"rttvar": "5000",
"to": "100000"
}
}
]
},
...
```
So the challenge is to simplify the output, removing the extraneous `$` properties:
```json
{
"status": [
{
"state": "up",
"reason": "arp-response",
"reason_ttl": "0"
}
],
```
Good luck!
* * *
In case you are wondering what the end-game of this might be, I've two reasons. One is that the extraneous `$` objects are common artefacts of converting from XML to JSON and so it isn't uncommon to want to get rid of them.
The second is that I am trying to automatically capture and update the devices on my network. I am combining this with some reference data that I maintain in an Excel spreadsheet detailing IP & MAC addresses with descriptions.
Ultimately, I will also combine this with 433MHz devices identified via my RFXtrx433E and devices on my Drayton Wiser smart heating system. Then I will have a complete picture of the devices I am interested in on my Home Automation system.
I will do a write-up of how to combine two arrays of objects using JSONata on my blog shortly which will hopefully be a useful reference for people trying to do similar tasks.
By the way, here is what I've come up with so far - but note that it just side-steps the challenge by building a new array of objects.
> **Spoiler**
>
> ```auto
> payload.nmaprun.host.(
> {
> /* Note different lookup methods */
> "ip": address.*[addrtype="ipv4"].addr,
> "mac": address.*[addrtype="mac"].addr.$uppercase(),
> "vendor": address.*.vendor,
> "hostname": hostnames.hostname."$".name,
> /* srtt is in microseconds so convert to milliseconds. /1000 again to get to seconds */
> "srtt": $parseInteger(times."$".srtt, '#0')/1000,
> /* Add a JS timestamp for when this was run
> * Eventually will use this to track last updates for devices. */
> "timestamp": $parseInteger($$.payload.nmaprun."$".start, '#0')*1000
> }
> )
>
> ```
>
> Note that the other thing I'd like to do to this is to turn the array into an object keyed on mac address.
---
### Author:  [@afelix](https://discourse.nodered.org/u/afelix)
#### Post date: [16 February 2020 14:52 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/2 "2020-02-16T14:52:52Z")
JSONata 1.8, yet to be included with Node-RED is introducing [a parent operator](https://github.com/jsonata-js/jsonata/releases/tag/v1.8.0), which makes paging through the tree a lot easier/readable. I've done queries like this before through rewriting (once you reach your first 100 line expression with an xml node it gets easy), in a similar way as your solution. Let me see if I can come up with a different one in a bit, I do have some ideas but they need a bit of experimentation
* * *
Unfortunately, my fingers aren't cooperating with my mind today, and I can't manage to get the syntax correct. The idea would be to utilise JSONata's capability of writing recursive functions, and have a function that for every object will look for a key "$", and get the underlying value out and inserted on parent level.
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [16 February 2020 20:33 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/3 "2020-02-16T20:33:28Z")
Thanks for looking Lena.
For now, I gave up (as often the case with JSONata) and went back to a familiar function node. A lot quicker to deal with! But I have to set myself (and others 😀) a challenge from time-to-time.
Anyway, the output from today's and yesterday's efforts has been to produce a new `devices` structure. This combines my manual IP/Mac table with nmap discovery. I've started to build a uibuilder editable table so that I will be able to update things like descriptions and locations for devices.
I blame Paul @zenofmud for this distraction because I looked again at his excellent "[ET Display Home](https://discourse.nodered.org/t/et-display-home-a-flow-to-help-manage-your-espeasy-sonoff-tasmota-and-homie-flashed-devices-now-on-github/1893/83)" and decided that I wanted a version that didn't take up 300 tabs and that would use uibuilder instead of Dashboard 😀
That then reminded me that I'd promised myself that I'd re-engineer my home automation reference data which in turn reminded me that I really needed a UI to administer all this stuff rather than manually editing it in the Editor.
Oh dear, what was that about addiction?!
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [16 February 2020 23:26 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/4 "2020-02-16T23:26:16Z")
You know, I did look at trying the figure out how to do it with JSONata but was stumped myself (not a difficult task when JSONata is involved) and decided if I was going to do, I'd do it with some function nodes myself.
Then I went for a 4 mile walk instead 😇
---
### Author:  [@markano](https://discourse.nodered.org/u/markano)
#### Post date: [23 February 2020 07:21 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/5 "2020-02-23T07:21:37Z")
Finally had some time to work on this. I'm not totally happy with the result, but I look forward to adding more in the coming weeks.
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [23 February 2020 07:25 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/6 "2020-02-23T07:25:47Z")
Note that the xml node can also assign a different value in stead of the $
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [23 February 2020 13:48 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/7 "2020-02-23T13:48:38Z")
Look forward to seeing the results 😀
> [@bakman2](#):
>
> Note that the xml node can also assign a different value in stead of the $
Doesn't really help though does it? You still get the extra level of object structure as the attributes are turned into `...$.{...}`. In the example case, I know that there is no clash between XML tag names and attribute names so the whole lot could be flattened significantly making it much easier to process further.
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [23 February 2020 18:08 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/8 "2020-02-23T18:08:08Z")
You're right I misread, you want to remove the attribute "enclosure", I agree it can be quite messy at times (looking at my zwave xml, it is hard to read through it)
I think you need to rewrite the "definition" of the json output, something in this direction (my jsonata skills are terrible, but you get the gist):
```auto
{
"status": **.(
$state := **.state;
$reason := **.reason;
"status".{
"state" : $state,
"reason": $reason
}
)
}
```
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [23 February 2020 23:26 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/9 "2020-02-23T23:26:08Z")
Cool. Well done, that's the best yet 😀
Somewhat extended:
```auto
{
"status": **.(
$state := **.state;
$reason := **.reason;
$addr := **.addr;
$vendor := **.vendor;
$srtt := **.srtt;
$hostname := **.name;
"status".{
"state" : $state,
"reason": $reason,
"address": $addr[0],
"mac": $addr[1],
"vendor": $vendor,
"srtt": $srtt,
"hostname": $hostname
}
)
}
```
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [23 April 2020 23:26 UTC](https://discourse.nodered.org/t/sunday-jsonata-challenge/21793/10 "2020-04-23T23:26:09Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.