{{ 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; and 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  ^ Description  ^
|Object per row, array from objects|Each row of the source dataset is used to construct a flat JSON object in which every column value corresponds to one object property. See Example #1, below.\\ \\ If the source dataset contains multiple rows, they will be converted into an array of JSON objects where each object corresponds to one row. See Example #2, below.|
|Array per column|The source dataset is converted into a new dataset in which values of each column are rolled up into a JSON array. See Example #3, below.|

\\
===="Object per row, array from objects" settings====
^ Setting  ^ Description  ^
|Column name|Enter the name of the column that will contain the constructed JSON.|
|Make an array even for single object|When checked, the result is always a JSON array, even if the source dataset (or a group) has only one row. When unchecked, a single row produces a single JSON object. See Example #4, below.|
|Group by selected columns|When checked, select the column(s) to group by. The result contains one row per group: the group column(s), followed by the JSON column constructed from the rows of that group. The group columns are not included in the JSON objects. See Example #6, below.|

\\
===="Array per column" settings====
^ Setting  ^ Description  ^
|Exclude nulls from arrays|When checked, empty values are left out of the arrays. When unchecked, they are included as //null//. See Example #5, below.|
|Group by selected columns|When checked, select the column(s) to group by. The result contains one row per group: the group column(s), followed by one column per remaining column, each containing a JSON array of that column's values in the group. See Example #7, below.|

\\
=====Remarks=====
====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.

\\
=====Examples=====
====Example #1====
>Construct a flat JSON object from the tabular dataset.

===Before (source table)===
^ Name ^ Kingdom  ^ Phylum ^ Class ^ 
| Rabbit |Animalia | Chordata | Mammalia  |

===After (result table)===
<code>
{
  "Name":"Rabbit",
  "Kingdom":"Animalia",
  "Phylum":"Chordata",
  "Class": "Mammalia",
}
</code>

===Action parameters===
>Mode:  Object per row

\\
====Example #2====
>Create flat JSON objects (inside an array) from the multi-row tabular dataset.

===Before (source table)===
^ ID ^ Name ^
| 1 | Apple  |
| 2 | Orange |

===After (result table)===
<code>
[
  {
    "ID":1,
    "Name":"Apple"
  },
  {
    "ID":2,
    "Name":"Orange"
  }
]
</code>

===Action parameters===
>Mode:  Object per row

\\
====Example #3====
>Convert the source table into a dataset in which values of each column are rolled up into a JSON array.
===Before (source table)===
^ ID ^ Name ^
| 1 | Apple  |
| 2 | Orange |

===After (result table)===
^ ID ^ Name ^
| [1,2] | ["Apple","Orange"]  |

===Action parameters===
>Mode: Array per column

\\
====Example #4====
>Always return an array, even when the table has only one row (useful for APIs that expect an array). Without this option, the result would be a single object: {"ID":1,"Name":"Apple"}

===Before (source table)===
^ ID ^ Name ^
| 1 | Apple  |

===After (result table)===
<code>
[
  {
    "ID":1,
    "Name":"Apple"
  }
]
</code>

===Action parameters===
>Mode: Object per row, array from objects
>Make an array even for single object: checked

\\
====Example #5====
>Leave empty values out of the arrays. Without this option, the "Discount" column would contain [10,null,5].

===Before (source table)===
^ ID ^ Name ^ Discount ^
| 1 | Apple  | 10 |
| 2 | Orange |    |
| 3 | Banana | 5  |

===After (result table)===
^ ID ^ Name ^ Discount ^
| [1,2,3] | ["Apple","Orange","Banana"] | [10,5] |

===Action parameters===
>Mode: Array per column
>Exclude nulls from arrays: checked

\\
====Example #6====
>Construct one JSON per customer. Bob has only one row, so his value is a single object. Check "Make an array even for single object" to get [{"Product":"Banana","Qty":2}] instead.

===Before (source table)===
^ Customer ^ Product ^ Qty ^
| Alice | Apple  | 3 |
| Alice | Orange | 1 |
| Bob   | Banana | 2 |

===After (result table)===
Alice:

<code>
[
  {
    "Product":"Apple",
    "Qty":3
  },
  {
    "Product":"Orange",
    "Qty":1
  }
]
</code>

Bob:

<code>
{
  "Product":"Banana",
  "Qty":2
}
</code>

===Action parameters===
>Mode: Object per row, array from objects
>Group by selected columns: Customer

\\
====Example #7====
>Roll up the values of each column into arrays per customer. In this mode, an array is created even for a group with a single row.

===Before (source table)===
^ Customer ^ Product ^ Qty ^
| Alice | Apple  | 3 |
| Alice | Orange | 1 |
| Bob   | Banana | 2 |

===After (result table)===
^ Customer ^ Product ^ Qty ^
| Alice | ["Apple","Orange"] | [3,1] |
| Bob   | ["Banana"] | [2] |

===Action parameters===
>Mode: Array per column
>Group by selected columns: Customer



\\ 
=====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()]]