This is an old revision of the document!
Table of Contents
CONSTRUCT JSON
Category: Transform / Advanced
Description
This action constructs a JSON object from a tabular dataset. It has two modes:
- Object per row, array from objects
- Array per column
A constructed JSON object is effectively a regular text value that is stored in a datagrid cell in EasyMorph.
Action settings
Mode: "Object per row"
In this mode, each row of the source dataset is used to construct a flat JSON object in which every column value corresponds to one object property. For instance, the dataset below:
Name | Kingdom | Phylum | Subphylum | Class | Order | Family |
---|---|---|---|---|---|---|
Rabbit | Animalia | Chordata | Vertebrata | Mammalia | Lagomorpha | Leporidae |
is constructed as the following JSON:
{ "Name":"Rabbit", "Kingdom":"Animalia", "Phylum":"Chordata", "Subphylum":"Vertebrata", "Class": "Mammalia", "Order":"Lagomorpha", "Family": "Leporidae" }
If the source dataset contains multiple rows, they will be converted into an array of JSON objects where each object corresponds to one row. Example:
ID | Name |
---|---|
1 | Apple |
2 | Orange |
Result:
[ { "ID":1, "Name":"Apple" }, { "ID":2, "Name":"Orange" } ]
Mode: "Array per column"
In this mode, the source dataset is converted into a new dataset in which values of each column are rolled up into a JSON array. For instance, the table from the example above would be converted into the following table:
ID | Name |
---|---|
[1,2] | [Apple,Orange] |
Data type conversion
EasyMorph data types are converted to JSON types as follows:
EasyMorph | Example | JSON | Example |
---|---|---|---|
Text | ABC | string | "ABC" |
Number | 123.45 | number | 123.45 |
Number (formatted as date) | 2020-Jan-10 | date | 2020-01-10T00:00:00 |
Boolean | TRUE | boolean | true |
Empty | null | null | |
Error | #Division by zero | Fails to convert |
Note that the action fails if the source dataset contains an error value.
Nesting JSON objects
EasyMorph automatically detects if a text value is already a JSON object or a JSON array. In this case, the text value is inserted verbatim, i.e. without wrapping in double quotes. This feature allows creating complex hierarchical JSON objects that nest other JSON objects. For instance converting the table below:
Track | Country | State/province | City | Location | Hiatus |
---|---|---|---|---|---|
Circuit Gilles Villeneuve | Canada | QC | Montreal | {"lat":45.500578, "long":-73.522461} | [1987, 2009] |
would produce the following JSON:
{ "Track":"Circuit Gilles Villeneuve", "Country":"Canada", "State/province":"QC", "City":"Montreal" "Location": { "lat":45.500578, "long":-73.522461 }, "Hiatus": [1987, 2009] }
Notice that "Location" is inserted as a JSON object, not as text. Also, the field "Hiatus" is inserted as a JSON array, not as text.