{{ transformations:ConstructJsonAction.png}}
======CONSTRUCT JSON======
Category: Transform / Web\\

\\ 
=====Description=====
This action constructs a [[https://en.wikipedia.org/wiki/JSON|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:

<code>
{
  "Name":"Rabbit",
  "Kingdom":"Animalia",
  "Phylum":"Chordata",
  "Subphylum":"Vertebrata",
  "Class": "Mammalia",
  "Order":"Lagomorpha",
  "Family": "Leporidae"
}
</code>

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:
<code>
[
  {
    "ID":1,
    "Name":"Apple"
  },
  {
    "ID":2,
    "Name":"Orange"
  }
]
  
</code>
\\ 
==== 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:

<code>
{
  "Track":"Circuit Gilles Villeneuve",
  "Country":"Canada",
  "State/province":"QC",
  "City":"Montreal"
  "Location": {
    "lat":45.500578, 
    "long":-73.522461
  },
  "Hiatus": [1987, 2009]
}
</code>
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.

\\ 
=====Community examples=====
  * [[https://community.easymorph.com/t//1279/6|Example: Constructing JSON]] ([[https://community.easymorph.com/uploads/short-url/qlhc3zdusCyuxWf2u0h0dqvgo0v.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //comments//; Action position: //2//)
  * [[https://community.easymorph.com/t//1661/1|How to create JSON for Airtable API]] ([[https://community.easymorph.com/uploads/short-url/kBrGoLczmNrnMt0SnXUhqOwjjRF.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //6//)
  * [[https://community.easymorph.com/t//2142/2|Constructing JSON issue]] ([[https://community.easymorph.com/uploads/short-url/aSWREiaUoC1qFEYRqTyu4Nwzdls.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Construct main JSON//; Action position: //4//)
  * [[https://community.easymorph.com/t//2641/1|How to publish real-time data to streaming dataset in Power BI]] ([[https://community.easymorph.com/uploads/short-url/22DTxfF295TVGQhGyvWGsHyadRt.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //5//)
  * [[https://community.easymorph.com/t/webrequest-and-parameters/2758/3|Community example:  Webrequest and parameters]]

\\ 
===== See also =====
  * [[transformations:parsejson|Parse JSON]]
  * [[syntax:functions:isjson|Functions:  IsJson()]]