# JSON Transformer

JSON Transformer transforms JSON using the JSONata query language

## Overview

The main purpose of the JSON Transformer is to transform JSON data using the [JSONata](https://jsonata.org/) query language.

The connector uses [JSONata version 1.8.6](https://docs.jsonata.org/1.8.0/overview).

## Introduction to the JSONata language

> **Info:** This page is primarily an introduction to the basics of the JSONata query language.
> Please also see our [Data Transformation Guide](#) which features a growing list of use-case based examples of putting JSONata to work.

The JSON Transformer Connector is an implementation of the JSONata library. 
This powerful tool allows you to take JSON data and do many things including:

* Reformatting / restructuring JSON
* Querying JSON for values
* Applying calculations to JSON data
* Even writing your own functions in JSONata!
  The documentation for JSONata is extensive and worth reviewing - at minimum, we recommend [](https://www.youtube.com/watch?v=ZBaK40rtIBM). 
  Below we will provide a gradual and simplified introduction to JSONata using a variety of examples with data you might see in Tray.

## JSONata Sandbox

These examples will be linked in a test / demo site ([try.jsonata.org](https://try.jsonata.org)) which is highly recommended when testing and troubleshooting your queries. 
You can set the connector version on the exerciser before writing your code.
![jsonata-version](https://tray.ai/documentation/images/connectors/core/json-transformer/6t62LghIPvhMexXCwqHyl7_jsonata-version.png)
Once you are happy with the result on the exerciser, you can move the code to the Tray workflow.

> **Info:** Be sure when working with data from a Tray workflow that you copy / paste the output data log of the step containing the JSON you want to work with.
> Tray logs data can vary a bit from what you would get in other tools (usually adding a key of “data” or “result” at the top level of the object)

## Extracting data

### Walkthrough

We can start working with the following dataset of books:
![jsonata-library](https://tray.ai/documentation/images/connectors/core/json-transformer/6f0MkdGjH70aBsBIfFDKjz_jsonata-library.png)
Query link [here](https://try.jsonata.org/U03x9JxKY)
If we begin our query with `library`, you will see that:

* It matches the\*\* key name at the top level \*\*of the object
* So returns everything in the **'library'** value
* This is an object itself, containing **'books'**
* **'books'** is an **array of objects** representing each book.  

### Query next level down

If we add `.books` we get the next level down in the object structure:
![jsonata-library-books](https://tray.ai/documentation/images/connectors/core/json-transformer/AfY0ocUJJU1rUmMmSGsF5_jsonata-library-books.png)

### Extract values (single values)

So far so good, but now the interesting part - what if we put `.title` ?
![jsonata-library-books-title](https://tray.ai/documentation/images/connectors/core/json-transformer/6lqGAD8MF8X6TDKqlTERFm_jsonata-library-books-title.png)
We get an array of the titles of each book.

### Extract values (arrays)

What if we extract 'authors'?
![jsonata-library-books-authors](https://tray.ai/documentation/images/connectors/core/json-transformer/VyhgzIELdn6NIu4AFKWol_jsonata-library-books-authors.png)
We get a flat array of all the authors (a concatenation of all the 'authors' arrays into a single array).  
[](https://docs.jsonata.org/simple)

## Structuring output

### Grouping results

What if we needed to have the grouping by title preserved?
We add `[]` around authors to make the query `library.books.[authors]`with the following result:
![jsonata-library-books-authors-grouped](https://tray.ai/documentation/images/connectors/core/json-transformer/4SSz23HAyPW1LMIr6m6js7_jsonata-library-books--authors-.png)

### Including keys (field names)

What if we need our result to have a specific structure with keys named 'Title' and 'Authors'? 
We can modify our query to include specific keys by:

* putting the fields section of the query in between `\{\}`
* specifying the key names as strings in between `""`
* specifying the values as the query string

Here our query is:

```js
library.books.\{
    "Title": title,
    "Authors": authors
\}
```

![jsonata-library-books-title-authors-grouped](https://tray.ai/documentation/images/connectors/core/json-transformer/3HR0mh1WL3K1QTpyo7QVgP_jsonata-library-books-title-authors-grouped.png)

### Ungrouping results

By starting the previous query with `library.books.` we are grouping the results by 'books'.  
And the result returns the 'title' and 'authors' for each book. 
However if we remove the final `.` we remove the grouping:

```js
library.books\{
    "Title": title,
    "Authors": authors
\}
```

And we end up with two arrays which simply list the titles and authors of all the books:
![jsonata-library-books-title-authors-ungrouped](https://tray.ai/documentation/images/connectors/core/json-transformer/68e691NLKVqd1QUBSg9yvh_jsonata-library-books-title-authors-ungrouped.png)

[More information about filtering and structuring data](https://www.stedi.com/docs/mappings/jsonata-cheatsheet)

### Forcing a nested array

There is a known bug within JSONata which will can cause errors if you are trying to nest a single array within an array. You will need to add a pair of square brackets `[]` at the end of your query to mitigate this.
The sample code below forces a single JSONata item into a nested array. Here is the code within the [JSONata Exerciser](https://try.jsonata.org/bQw5ybxtN) itself for a closer look.

```json
list.[\{"key": "ContactId", "value": contact.Id\},\{"key": "CampaignId", "value":  "7014V0000025zX5QAI"\}][]
```

## Operators

JSONata has a list of simple operators which allow you to combine data and run simple comparisons.
Here we will take you through some examples of putting operators to use, and provide links to the full list of operators in the JSONata documentation.

### Path Operators

### Full list of path operators

<https://docs.jsonata.org/path-operators>

### The . operator (Map)

The above introduction to extracting and structuring data will have familiarized you with the `.` operator:

* It enables you to specify a path in your JSON data (e.g. we want to look at all results in `library.books`
* It also means 'for each' so it can be used to group results by item
  When using batch update operations, some APIs want data in specific formats, for example in objects with keys called 'key' and 'value'.
  The below shows an example of how to do this. Please see our [Data Transformation Guide](#) for more examples of how to structure data and satisfy the input schema of services and databases:
  ![jsonata-library-books-key-value-pairs](https://tray.ai/documentation/images/connectors/core/json-transformer/7r3ExoZsnQgOGvNrVEQ1cv_jsonata-library-books-key-value-pairs.png)

### Other operators

### Full list of other operators

<https://docs.jsonata.org/other-operators>

### Example - the & operator (concatenate)

The & operator will join two or more strings into a single string:
![jsonata-concatenate](https://tray.ai/documentation/images/connectors/core/json-transformer/D6oxjCcqVj4JID3xXLVU4_jsonata-concatenate.png)

### Numeric operators

### Full list of numeric operators

<https://docs.jsonata.org/numeric-operators>

| operator | purpose            |
| -------- | ------------------ |
| +        | addition           |
| -        | subtraction        |
| \*       | multiplication     |
| /        | division           |
| %        | remainder (modulo) |
| ..       | range              |

### Example - the \* operator (multiplication)

The source JSON data in this example includes order information, with items and quantity:

### Calculate totals for each product

![jsonata-price-times-quantity](https://tray.ai/documentation/images/connectors/core/json-transformer/3OM9ouXN4owCsCU0XMHMS6_jsonata-price-times-quantity.png)

### Sum all totals

With the inclusion of one of the functions, **$sum**, covered below, we can get a total:
![jsonata-sum-totals](https://tray.ai/documentation/images/connectors/core/json-transformer/5RPzvxh0nAZLzQtoscW08l_jsonata-sum-totals.png)

##

## Functions

JSONata also has a list of functions, which are more complex and powerful than operators.
They are in the format of `$function()`
Where 'arguments' must be passed inside the `()`
For example in `$length("Hello World")` it is "Hello World" that is being passed as the argument.
This gives the answer of '11' as there are 11 characters in "Hello World" (including the space).

### String functions

### List of all string functions

<https://docs.jsonata.org/string-functions>

### Example - $contains( )

Query link [here](https://try.jsonata.org/Yfq0wqSkW)
This function can be used to find objects which contain a certain string.
Here we are looking for all 'fields' blocks where the 'text' value contains "campaign":
![jsonata-contains](https://tray.ai/documentation/images/connectors/core/json-transformer/22CcauBPIANGttcckTEIiH_jsonata-contains.png)

###

### Example - $subStringAfter( )

Query link [here](https://try.jsonata.org/95wHsHa6T)
This function can be used to extract text after a certain string.
So we could extend the previous contains( ) example:
![jsonata-substringafter](https://tray.ai/documentation/images/connectors/core/json-transformer/1oQmFnZQkgRtAQeJTxB46S_jsonata-substringafter.png)

### Numeric aggregation functions

### Full list of numeric aggregation functions

<https://docs.jsonata.org/aggregation-functions>

### Example - $sum( )

The source JSON data in this example includes order information, with items and quantity:

### Calculate totals for each product

We can first use the \* operator as explained in 'Operators' above:
![jsonata-price-times-quantity](https://tray.ai/documentation/images/connectors/core/json-transformer/3OM9ouXN4owCsCU0XMHMS6_jsonata-price-times-quantity.png)

### Sum all totals

Wrapping the entire query in a \*\*$sum( ) \*\*function we can get a total:
![jsonata-sum-totals](https://tray.ai/documentation/images/connectors/core/json-transformer/5RPzvxh0nAZLzQtoscW08l_jsonata-sum-totals.png)

### Boolean functions

### Full list of boolean functions

<https://docs.jsonata.org/boolean-functions>

### Example - $boolean( )

Query link [here](https://try.jsonata.org/MZh1q2-NS)
As per [this table](https://docs.jsonata.org/boolean-functions#boolean) if a number is passed to the boolean function it will respond with:

* false if 0
* true it not 0
  So in the following example, by using `library.books.copies.$boolean()` we can get a quick report on any book titles that are missing copies.
  ![function-boolean](https://tray.ai/documentation/images/connectors/core/json-transformer/4eZBFN8xEPZrf7MUPRqYzJ_function-boolean.png)
  Try enhancing this into a more detailed report using:

```json
library.books.\{
    "title": title,
    "in_stock": copies.$boolean()
    \}
```

### Example - $exists( )

Query example [here](https://try.jsonata.org/IsNcVSUZC)
This can be used to check if a property actually exists, such as the following for each object in an array `library.loans.($exists(isbn))`:
![function-exists](https://tray.ai/documentation/images/connectors/core/json-transformer/5erj0nB5HiOhgyxNr64wjB_function-exists.png)
Try enhancing this into a more detailed report using:

```json
library.loans.\{
    "customer": customer,
    "isbn_present": ($exists(isbn))
\}
```

### Array functions

### Full list of array functions

<https://docs.jsonata.org/array-functions>

### Example - $count( )

Query link [here](https://try.jsonata.org/KNF9SKAdS)
A good use for this function is to count the number of items in an array which meet a certain condition.
Like here where we use `$count([library.books[copies=0]])` to count how many books have 0 copies:
![function-array-count](https://tray.ai/documentation/images/connectors/core/json-transformer/6pGCNKO5AC4zvAoj0ymYmu_function-array-count.png)

### Object functions

### Full list of object functions

<https://docs.jsonata.org/object-functions>

### Example - $keys( )

### Extract all keys

Query link [here](https://try.jsonata.org/-ICCk8416)
This function can be used to extract all keys from an object.
The following example uses the for each `.` combined with `[ ]` to go through an array of books and create an array of keys for each object:
![function-object-keys](https://tray.ai/documentation/images/connectors/core/json-transformer/5bcO4YPuJQmgMNQd31ocD0_function-object-keys.png)

### Get distinct keys

We can extend this example by removing the `[ ]` to turn the result into a single object of all keys and then chaining it to the `$distinct( )` function like this:
`library.books.$keys() ~> $distinct()`
![function-keys-distinct](https://tray.ai/documentation/images/connectors/core/json-transformer/6UK4vKZTnN3DAmeW3vBAJ0_function-keys-distinct.png)

## Best practices and tips

### Chaining queries

The `~>` operator is a very useful way of simplifying your queries.
If you want to make use of more than one function or operation it can allow you to pass the result of one as the input for the next.

### Example - complex single query

Query link [here](https://try.jsonata.org/HHUIJ07mJ)
In the following example we are using three functions to extract the name of a customer's email provider and convert it to uppercase.
Trying to do it one query:
`$uppercase($substringBefore($substringAfter(customer.email, "@"), "."))`
Is confusing and unintuitive:
![chain-example-complex](https://tray.ai/documentation/images/connectors/core/json-transformer/69ILAJqqHfLmBrYoGtK5EH_chain-example-complex.png)

### Example - simplified chain of queries

Query link [here](https://try.jsonata.org/94JOZzc6_)
So we can chain these functions one after the other:
`customer.email ~> $substringAfter("@") ~> $substringBefore(".") ~> $uppercase()`
The functions are now in a logical order, and it is clear what each is doing:
![chain-example-simplified](https://tray.ai/documentation/images/connectors/core/json-transformer/70bTCIzIy0tgp8ZzEOG2cA_chain-example-simplified.png)

### Creating variables

Variables are useful whenever you want to store some piece of information and require it further down in your query.
Variables begin with `$` and are assigned using `:=`
`$name := customer.name`
`$age := customer.age`

### Example 1

[In the following example](https://try.jsonata.org/2ECeKpaZM), we are assigning a variable to help us report on outstanding loans of library books.
The `$loansCount :=` variable is assigned using the `$count( )` function to calculate how many total loans there are.
We then use the `>` comparison operator and `?` conditional operator to establish if `loansCount` is above or below the threshold of 10:
![variables-example-oustanding-loans](https://tray.ai/documentation/images/connectors/core/json-transformer/7ovtdI5lQSnw6IuJYg4dH8_variables-example-oustanding-loans.png)

### Example 2

[Here](https://try.jsonata.org/VmmCk8p70) is a use case where campaignId and blockId have been extracted from a piece of data.
The `$matchingObject :=` variable is assigned using the `$contains( )` function to find the object which has 'campaign' in `fields.text.`
The `$matchingObject :=` variable can then be used when assigning the `$campaignId` and `$blockId` variables:
![variables-example-2](https://tray.ai/documentation/images/connectors/core/json-transformer/2DTsDjqrOpXY2qsVooEcqH_variables-example-2.png)

### Defining your own functions

If your queries are getting too involved even for chaining, you can define your own functions.
This will make your queries more readable and easy to structure.
Defining functions is done by naming and declaring the function in the following format:
`$functionName := ``**function**``(<required variables>){<build function using required variables>}`

Note the use of `( )` to set the required variables and `{ }` to build the function itself.

### Example - defining a function and using on a single object

Query link [here](https://try.jsonata.org/c2SG2xfDg)
We can extend the above chaining queries example to build two functions which:

* Retrieve and uppercase the domain name
* Concatenate first and last name
  ![create-functions-example-simple](https://tray.ai/documentation/images/connectors/core/json-transformer/5EMrs6IUhhqtbhVbkNnfW1_create-functions-example-simple.png)

### Example extended - using on an array

Query link [here](https://try.jsonata.org/FOvvD9_cy)
If we have an array of 'customers' we can begin the query with `customers. `so the defined functions and query will run for each customer in the array:
![create-functions-example](https://tray.ai/documentation/images/connectors/core/json-transformer/5roIvXK58BeCxxV52ue1ct_create-functions-example.png)

### Accessing arrays / objects with no name

If you have a payload which presents just a simple array, you can access this using the `$$` root variable:
![root-variable](https://tray.ai/documentation/images/connectors/core/json-transformer/vjlazTmfXu3M0Q3MTHcuH_root-variable.png)
Please see the section on the [built-in variables in the Stedi cheatsheet](https://www.stedi.com/docs/mappings/jsonata-cheatsheet#17-variables) for more info.

##

Notes on using the JSON Transformer
When using the JSON Transformer in your workflows, you can use the connector snake to set the input data for the JSONata query:
![jsontransformer workflowusage](https://tray.ai/documentation/images/connectors/core/json-transformer/7jo9VGjP2c7IOxDnRmRYlQ_jsontransformer_workflowusage.png)
You can also set this to an object and use multiple datasets for your query to reference:
![simple-map-values-jsonata-script](https://tray.ai/documentation/images/connectors/core/json-transformer/1iDSbFwgy2gvamz61yPXoS_simple-map-values-jsonata-query.png)