Removing elements from JSON, including bringing child elements up a level?

General
1

I have a JSON string that's being returned by a HTTP Request to an external service. I have a format into which I need this JSON string to be parsed for a library that comes downstream in my flow. I'm not sure where to start on how to manipulate the JSON in node red to remove the elements I don't need, and additionally to move some child elements up to the top.

Any pointers, help, or examples would be very much appreciated.

Input string example:

{
  "objectIdFieldName" : "OBJECTID", 
  "uniqueIdField" : 
  {
    "name" : "OBJECTID", 
    "isSystemMaintained" : true
  }, 
  "globalIdFieldName" : "", 
  "geometryType" : "esriGeometryPoint", 
  "spatialReference" : {
    "wkid" : 102100, 
    "latestWkid" : 3857
  }, 
  "fields" : [
    {
      "name" : "StationNum", 
      "type" : "esriFieldTypeInteger", 
      "alias" : "StationNum", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }, 
    {
      "name" : "Height", 
      "type" : "esriFieldTypeDouble", 
      "alias" : "Height", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }
  ], 
  "features" : [
    {
      "attributes" : {
        "StationNum" : 558105, 
        "Height" : 1.11
      }
    }, 
    {
      "attributes" : {
        "StationNum" : 558018, 
        "Height" : 0.92
      }
    }, 
    {
      "attributes" : {
        "StationNum": 558019,
        "Height": 1.82
       }
   ]

What I actually need is just the part from the 'features' section, but I also need to remove the "attributes": {} element. Ie, bring the StationNum and Height up a level. I've also noticed that the StationName, despite being a number, needs to be wrapped with quotes " ".

The top part of the JSON before "features" can be completely dropped.

Example of what I'm after:

[
    {
        "StationNum": "558105",
        "Height": 1.11
    },
    {
        "tag": "558018",
        "value": 0.92
    },
    {
        "tag": "558019",
        "value": 1.82
    },
]

EDIT:
Looks like a Change node + JSONata might be what I'm after, but am struggling to get it working.

2

The Jsonata would be

$$.payload.features#$i.attributes.StationNum.{
   ($i=0 ? "StationNum" : "tag"): $string($),
   ($i=0 ? "Height" : "value"): $$.payload.features[$i].attributes.Height
}

example flow

[{"id":"e7e22cfd.b654c","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":" {         \"objectIdFieldName\": \"OBJECTID\",         \"uniqueIdField\": {             \"name\": \"OBJECTID\",             \"isSystemMaintained\": true         },         \"globalIdFieldName\": \"\",         \"geometryType\": \"esriGeometryPoint\",         \"spatialReference\": {             \"wkid\": 102100,             \"latestWkid\": 3857         },         \"fields\": [             {                 \"name\": \"StationNum\",                 \"type\": \"esriFieldTypeInteger\",                 \"alias\": \"StationNum\",                 \"sqlType\": \"sqlTypeOther\",                 \"domain\": null,                 \"defaultValue\": null             },             {                 \"name\": \"Height\",                 \"type\": \"esriFieldTypeDouble\",                 \"alias\": \"Height\",                 \"sqlType\": \"sqlTypeOther\",                 \"domain\": null,                 \"defaultValue\": null             }         ],         \"features\": [             {                 \"attributes\": {                     \"StationNum\": 558105,                     \"Height\": 1.11                 }             },             {                 \"attributes\": {                     \"StationNum\": 558018,                     \"Height\": 0.92                 }             },             {                 \"attributes\": {                     \"StationNum\": 558019,                     \"Height\": 1.82                 }             }         ]     } ","payloadType":"json","x":250,"y":240,"wires":[["e37d0d84.e3df88"]]},{"id":"e37d0d84.e3df88","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.features#$i.attributes.StationNum.{\t   ($i=0 ? \"StationNum\" : \"tag\"): $string($),\t   ($i=0 ? \"Height\" : \"value\"): $$.payload.features[$i].attributes.Height\t}\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":240,"wires":[["d7bb6032.d2d198"]]},{"id":"d7bb6032.d2d198","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":680,"y":260,"wires":[]}]

output

[
  {
    "StationNum":"558105",
    "Height":1.11
  },
  {
    "tag":"558018",
    "value":0.92
  },
  {
    "tag":"558019",
    "value":1.82
  }
]

[edit] for only tag and value

$$.payload.features#$i.attributes.StationNum.{
   "tag": $string($),
   "value": $$.payload.features[$i].attributes.Height
}
1 Like
3

That's awesome! Thank you

I've also made a mistake here - I' accidentally preserved the first element in the example with regards to StationNum/Height. How would I go about modifying your code to ensure that ALL elements have tag/value?

4

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