Page Comparison

Overview

JSONata is a powerful data query and transformation language designed for processing JSON data. It is used to preprocess, merge, and format data retrieved from APIs, allowing developers to perform complex calculations and data manipulations (check their JSONata Exerciser).

On this page:

Table of Contents

minLevel	1
maxLevel	6
outline	false
style	disc
type	list
printable	true

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.

Code Block

language	json

{
  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.

Code Block

language	json

{type: 'json'}

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

Download this JSON response here:

View file

name	jsonata.json5

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.

Code Block

language	json

{
  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.

Code Block

language	json

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

Expand

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

Code Block

language	json

{
  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

Versions Compared

Old Version 7

New Version 8

Key

Overview

Key Features of JSONata

Step-by-step guide

Fetch data from an API endpoint

Add a chart

Debug Mode

Tips and Best Practices

Common Functions in JSONata

JSONata