Query and Transform your Data with JSONata

Step-by-step guide

Fetch data from an API endpoint

Use a REST node to fetch data from an API endpoint. This example will generate data for 3 people.

{
  type: 'rest',
  uri: 'https://randomuser.me/api/?results=3',
  id: 'people',

  children: [
    {type: 'json'}
    ]
}

In this example, we are using a node of type json.

{type: 'json'}

So we can display in our report the whole JSON response coming from the REST API. Whenever you need to see the data format coming from the rest API, you can use a JSON node.

Download this JSON response here:

Determine the new data you want to calculate, such as the decade of birth based on the date of birth.
Use JSONata functions to perform the necessary calculations. The map function is commonly used to iterate over each record in the results array.
Store the calculated data in a new key within the original data structure. For example, add a "decade" key to each record in the results array.
Perform additional calculations if needed, such as extracting distinct decades from the enriched data and sorting them. Store these values in separate keys, like decades.

This will generate a sorted list of the decades that these three people were born in. This example displays only two decades because two of the people generated in Step 1 were born in 1980.

Note that the API returns random users, so the results will change in each iteration.

{
  type: 'rest',
  uri: 'https://randomuser.me/api/?results=3',
  id: 'people',
  path: '(\
    /* Calculate the decade of birth for each person */\
    $withDecades := $map($.results, function($p) { $merge([{ "decade": $substring($formatDate($p.dob.date), -4, 3) & "0" }, $p]) });\
    \
    /* Extract decades range */\
    $decades := $sort($distinct($map($withDecades, function($p) { $p.decade })));\
    \
    { "data": $withDecades, "decades": $decades }\
  )',
  children: [
    {type: 'json'}
    ]
}

Add a chart

Add a chart node to the report (see Advanced Knowledge you need to build reports).
Define the chart's dimensions and appearance.
Set the style of the chart's grid.
Customize the axes' appearance.
Populate the X-axis values using data from the report context, such as the decades key.
Use JSONata functions to calculate the values for each data series in the chart. For example, calculate the number of male and female records per decade. Using the code below, the report displays a bar chart showing the number of males and females born in each decade.

data: '$map($.decades, function($d) {\
              $count(\
                $map(\
                  $filter(\
                    $filter($.data, function($p) { $p.gender = "male"}),\
                    function($p) { $d = $p.decade}),\
                  function($v) {$v.decade}))})',
          },

Full descriptor that displays a bar chart counting the number of males and females born in each decade.

{
  type: 'rest',
  uri: 'https://randomuser.me/api/?results=300',
  id: 'people',
  path: '(\
    /* Calculate the decade of birth for each person */\
    $withDecades := $map($.results, function($p) { $merge([{ "decade": $substring($formatDate($p.dob.date), -4, 3) & "0" }, $p]) });\
    \
    /* Extract decades range */\
    $decades := $sort($distinct($map($withDecades, function($p) { $p.decade })));\
    \
    { "data": $withDecades, "decades": $decades }\
  )',
  children: [
    {
      type: 'json'
    },
    {
      type: 'chart',
      options: {
        chart: {
          width: 700,
          height: 400,
          
        },
        cartesianGrid: {
          stroke: "#eee",
          strokeDasharray:"5 5",
          vertical: false
        },
        xAxis: {
          axisLine: false,
          tickLine: false
        },
        yAxis: {
          axisLine: false,
          tickLine: false
        },
      },
      data: {
        categories: '$.decades',
        series: [
          {
            chartType: 'bar',
            name: 'Males',
            stackId: 'a',
            fill: '#82ca9d',
            data: '$map($.decades, function($d) {\
              $count(\
                $map(\
                  $filter(\
                    $filter($.data, function($p) { $p.gender = "male"}),\
                    function($p) { $d = $p.decade}),\
                  function($v) {$v.decade}))})',
          },
          {
            chartType: 'bar',
            name: 'Females',
            stackId: 'a',
            fill: '#8884d8',
            data: '$map($.decades, function($d) {\
              $count(\
                $map(\
                  $filter(\
                    $filter($.data, function($p) { $p.gender = "female"}),\
                    function($p) { $d = $p.decade}),\
                  function($v) {$v.decade}))})',
          },
        ],
      },
    },
  ]
}

Debug Mode

Toggle Debug Mode to view additional buttons for inspecting the report context.
Right-click in the Report Description Editor and select Inspect to access the console.
Explore the JSON context at various levels:
- Root context: Contains global data sources and metadata.
- Node context: Contains data specific to the current processing node.

Tips and Best Practices

Start Small: Begin with simple queries to understand the structure of your data.
Leverage Debug Mode: Use the debug tools to trace errors and verify transformations.
Utilize Functions: JSONata provides a wide range of built-in functions for advanced data manipulation.
Reuse and Optimize: Store reusable queries in variables to simplify and optimize transformations.

Common Functions in JSONata

Function	Description	Example
`$map`	Maps a function to each element in an array.	`$map(data, $p -> $p.name)`
`$filter`	Filters elements based on a condition.	`$filter(data, $p -> $p.age > 20)`
`$merge`	Merges objects into a single object.	`$merge([$a, $b])`
`$distinct`	Returns unique values from an array.	`$distinct(data)`
`$sort`	Sorts an array.	`$sort(data)`
`$count`	Counts elements in an array.	`$count(data)`

JSONata

Explore the full potential of JSONata by visiting their official documentation for detailed guidance and advanced examples: https://docs.jsonata.org/overview.html

Overview

Key Features of JSONata