Transforming Data with JSONata

riplatt · 28 April 2021 02:11

Hi,
I'm trying to transform a API result to display on a worldmap.

The result looks like:

{
  "payload": [
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 64306,
      "probability_percent": 0.635,
      "group_id": "1"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 66156,
      "probability_percent": 0.574,
      "group_id": "1"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 68006,
      "probability_percent": 0.755,
      "group_id": "1"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 69856,
      "probability_percent": 0.665,
      "group_id": "1"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 2293556,
      "probability_percent": 0.72,
      "group_id": "2"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 2295406,
      "probability_percent": 0.562,
      "group_id": "2"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 2297256,
      "probability_percent": 0.636,
      "group_id": "2"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 2299106,
      "probability_percent": 0.548,
      "group_id": "2"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 2300956,
      "probability_percent": 0.742,
      "group_id": "2"
    },
    {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 2302806,
      "probability_percent": 0.655,
      "group_id": "2"
    },
etc...
   {
      "id": "115",
      "sample_time": "2021-04-25T14:00:00.000Z",
      "lat": -20.00,
      "lon": 150.00,
      "distance_mm": 3562656,
      "probability_percent": 0.584,
      "group_id": "11"
    }
]

and I need to transform it to something like:

[
    {
        "name": "group_1_mean",
        "layer": "leaks",
        "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
        },
        "radius": 67.081,
        "lat": -20.00,
        "lon": 150.00
    },
    {
        "name": "group_1_min",
        "layer": "leaks",
        "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
        },
        "radius": 64.306,
        "lat": -20.00,
        "lon": 150.00
    },
    {
        "name": "group_1_max",
        "layer": "leaks",
        "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
        },
        "radius": 69.856,
        "lat": -20.00,
        "lon": 150.00
    },
    {
        "command": {
            "lat": -20.00,
            "lon": 150.00,
            "zoom": 17
        }
    }
]

trying with JSONata (https://try.jsonata.org/KXi7Dpz2-) I got:

{
  "1": {
    "name": "group_1_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 67.081,
    "lat": -20,
    "lon": 150
  },
  "2": {
    "name": "group_2_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2298.181,
    "lat": -20,
    "lon": 150
  },
  "3": {
    "name": "group_3_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2466.531,
    "lat": -20,
    "lon": 150
  },
  "4": {
    "name": "group_4_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2477.631,
    "lat": -20,
    "lon": 150
  },
  "5": {
    "name": "group_5_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2498.906,
    "lat": -20,
    "lon": 150
  },
  "6": {
    "name": "group_6_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2591.406,
    "lat": -20,
    "lon": 150
  },
  "7": {
    "name": "group_7_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2657.081,
    "lat": -20,
    "lon": 150
  },
  "8": {
    "name": "group_8_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2666.331,
    "lat": -20,
    "lon": 150
  },
  "9": {
    "name": "group_9_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2692.231,
    "lat": -20,
    "lon": 150
  },
  "10": {
    "name": "group_10_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 2727.381,
    "lat": -20,
    "lon": 150
  },
  "11": {
    "name": "group_11_mean",
    "layer": "mylayer",
    "options": {
      "stroke": true,
      "color": "#910000",
      "weight": 4,
      "opacity": 0.5,
      "fill": false,
      "fillColor": null,
      "fillOpacity": 0.2
    },
    "radius": 3541.381,
    "lat": -20,
    "lon": 150
  }
}

by using the JSONata:

payload {
    `group_id`: {
        "name": "group_" & $distinct(group_id) & "_mean",
        "layer": "mylayer",
        "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
        },
        "radius": $average(distance_mm) / 1000,
        "lat": $distinct(lat),
        "lon": $distinct(lon)
    }
}

But I can not workout how to remove the group numbers i.e. { "1":{}, "2":{}}.

So just reaching out to see if anyone has any ideas.

Thank for the help.

janvda · 28 April 2021 06:59

I think the following jsonata expression will do the job:

https://try.jsonata.org/zfinAbvJ0

Here below I have copy pasted the same jsonata expression used in above link.

(
    $group_ids := payload.group_id~>$distinct();
    $group_ids@$id.(
        $payload_for_group_id := payload[ group_id = $id];
        {
          "name": "group_" & $id & "_mean",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$average())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        };
    );
)

janvda · 28 April 2021 07:06

one issue with above expression:

in case there is only one group_id in the list then it won't return an array with a single element but it will directly return the single element"

To assure that it also returns an array in case there is only one group_id you must just add [] at the end.

Note also that in case there are not group_ids in the array (so an empty array) then it won't return an empty array - it just doesn't return anything (no match) !

(
    $group_ids := payload.group_id~>$distinct();
    $group_ids@$id.(
        $payload_for_group_id := payload[ group_id = $id];
        {
          "name": "group_" & $id & "_mean",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$average())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        };
    )[];
)

riplatt · 28 April 2021 07:15

Thanks @janvda,
Just reading through and testing; One quick question if you know the answer and this is my fault for not giving a link to the json test, but if I wanted to also add circles for min and max so I would get a group_1_min and group_1_max, how would I do that with what you have done, Thanks.

updated json test https://try.jsonata.org/uJO-M9h7Z showing the way I'm trying to build the worldmap input.

payload {
    `leak_group`: [
        {
            "name": "group_" & $distinct(leak_group) & "_mean",
            
            "radius": $average(distance_mm) / 1000,
            "lat": $distinct(lat),
            "lon": $distinct(lon),
            "color": "#910000",
            "weight": "1",
            "stroke": true,
            "fill": false,
            "clickable": false
        },
        {
            "name": "group_" & $distinct(leak_group) & "_min",
            "layer": "mylayer",
            "radius": $min(distance_mm) / 1000,
            "lat": $distinct(lat),
            "lon": $distinct(lon),
            "color": "#910000",
            "weight": "1",
            "stroke": true,
            "fill": false,
            "clickable": false
        },
        {
            "name": "group_" & $distinct(leak_group) & "_max",
            "layer": "mylayer",
            "radius": $max(distance_mm) / 1000,
            "lat": $distinct(lat),
            "lon": $distinct(lon),
            "color": "#910000",
            "weight": "1",
            "stroke": true,
            "fill": false,
            "clickable": false
        }
    ],
    "command": {
        "lat": $distinct(lat),
        "lon": $distinct(lon),
        "zoom": 17
    }
}

riplatt · 28 April 2021 07:24

Looks like

(
    $group_ids := payload.group_id~>$distinct();
    $group_mean := $group_ids@$id.(
        $payload_for_group_id := payload[ group_id = $id];
        {
          "name": "group_" & $id & "_mean",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$average())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        };
    );
    $group_max := $group_ids@$id.(
        $payload_for_group_id := payload[ group_id = $id];
        {
          "name": "group_" & $id & "_max",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$max())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        };
    );
    $group_min := $group_ids@$id.(
        $payload_for_group_id := payload[ group_id = $id];
        {
          "name": "group_" & $id & "_min",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$min())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        };
    );
    $sort([
        $group_mean,
        $group_max,
        $group_max
    ], function($l, $r){$l.name > $r.name})
)

will do 90% what I need thanks for the help.

janvda · 28 April 2021 08:04

I think this will also work:

(
    $group_ids := payload.group_id~>$distinct();
    $group_ids@$id.(
        $payload_for_group_id := payload[ group_id = $id];
        [{
          "name": "group_" & $id & "_mean",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$average())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        },
        {
          "name": "group_" & $id & "_min",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$min())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        },
        {
          "name": "group_" & $id & "_max",
          "layer": "mylayer",
          "options": {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           },
          "radius": ($payload_for_group_id.distance_mm~>$max())/1000,
          "lat": $payload_for_group_id.lat~>$distinct(),
          "lon": $payload_for_group_id.lon~>$distinct()
        }
        ]
        ;
    );
)

janvda · 28 April 2021 08:10

And a bit shorter using variables $lat, $lon and $options.

(
    $group_ids := payload.group_id~>$distinct();
    $group_ids@$id.(
        $payload_for_group_id := payload[ group_id = $id];
        $lat := $payload_for_group_id.lat~>$distinct();
        $lon := $payload_for_group_id.lon~>$distinct();
        $options := {
            "stroke": true,
            "color": "#910000",
            "weight": 4,
            "opacity": 0.5,
            "fill": false,
            "fillColor": null,
            "fillOpacity": 0.2
           };
        [{
          "name": "group_" & $id & "_mean",
          "layer": "mylayer",
          "options": $options,
          "radius": ($payload_for_group_id.distance_mm~>$average())/1000,
          "lat": $lat,
          "lon": $lon
        },
        {
          "name": "group_" & $id & "_min",
          "layer": "mylayer",
          "options": $options,
          "radius": ($payload_for_group_id.distance_mm~>$min())/1000,
          "lat": $lat,
          "lon": $lon
        },
        {
          "name": "group_" & $id & "_max",
          "layer": "mylayer",
          "options": $options,
          "radius": ($payload_for_group_id.distance_mm~>$max())/1000,
          "lat": $lat,
          "lon": $lon
        }
        ]
        ;
    );
)

system · 27 June 2021 08:10

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

Topic		Replies	Views
Adapt JSON to worldmap format General	9	648	2 September 2019
Jsonata to chart when payload data has extra [ ] General	4	316	22 March 2022
JSONATA for chart General chart	8	463	8 June 2022
Help Parsing Data with JSONata General	13	2417	11 March 2019
Array into REST format for API call General	8	447	13 November 2019

Transforming Data with JSONata

Related topics