User Tools

Site Tools


transformations:constructjson

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
transformations:constructjson [2020/01/11 15:55] – [Nesting JSON objects] dmitrytransformations:constructjson [2025/02/10 18:55] (current) – revise to 2025 format craigt
Line 1: Line 1:
-===== Construct JSON =====+{{ transformations:ConstructJsonAction.png}} 
 +======CONSTRUCT JSON=====
 +Category: Transform / Web\\
  
-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 +=====Description===== 
-  * Array per column+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.\\
  
-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.  See Example #1, below.
  
-==== Mode "Object per row" ==== +If the source dataset contains multiple rows, they will be converted into an array of JSON objects where each object corresponds to one rowSee Example #2, below.
-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 propertyFor instancethe dataset below:+
  
-^ Name ^ Kingdom  ^ Phylum ^ Subphylum ^ Class ^ Order ^ Family ^  +\\  
-| Rabbit |Animalia | Chordata | Vertebrata | Mammalia  | Lagomorpha  | Leporidae |+==== 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. See Example #3, below.
  
-is constructed as the following JSON:+\\ 
 +=====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> <code>
 { {
Line 20: Line 72:
   "Kingdom":"Animalia",   "Kingdom":"Animalia",
   "Phylum":"Chordata",   "Phylum":"Chordata",
-  "Subphylum":"Vertebrata", 
   "Class": "Mammalia",   "Class": "Mammalia",
-  "Order":"Lagomorpha", 
-  "Family": "Leporidae" 
 } }
 </code> </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:+===Action parameters=== 
 +>Mode Object per row
  
 +\\
 +====Example #2====
 +>Create a flat JSON object from the multi-row tabular dataset.
 +
 +===Before (source table)===
 ^ ID ^ Name ^ ^ ID ^ Name ^
 | 1 | Apple  | | 1 | Apple  |
 | 2 | Orange | | 2 | Orange |
  
-Result:+===After (result table)===
 <code> <code>
 [ [
Line 45: Line 100:
   }   }
 ] ]
-   
 </code> </code>
  
-==== Mode "Array per column" ==== +===Action parameters=== 
-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:+>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 ^ ^ ID ^ Name ^
 | [1,2] | [Apple,Orange]  | | [1,2] | [Apple,Orange]  |
  
 +===Action parameters===
 +>Mode: Array per column
  
-==== 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:00Z | 
-| 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. +\\  
- +=====Community examples===== 
-==== Nesting JSON objects ==== +  * [[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//) 
-EasyMorph automatically detects if a text value is already a JSON object or a JSON arrayIn this case, the text value is inserted verbatim, i.e. **without** wrapping in double quotesThis feature allows creating complex hierarchical JSON objects that nest other JSON objectsFor instance converting the table below+  [[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//) 
-^ Track ^ Country  ^ State/province ^ City     ^ Location ^ Hiatus ^  +  * [[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//) 
-Circuit Gilles Villeneuve  Canada   | QC | Montreal  | {"lat":45.500578, "long":-73.522461}  [1987, 2009  | +  * [[https://community.easymorph.com/t/webrequest-and-parameters/2758/3|Community example:  Webrequest and parameters]]
- +
-would produce the following JSON:+
  
-<code> +\\  
-+===== See also =====
-  "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, field "Hiatus" is inserted as a JSON array, not as text. +
- +
-==== See also ==== +
-  * [[https://community.easymorph.com/t/example-constructing-json/1279/5|Example: Constructing JSON]]+
   * [[transformations:parsejson|Parse JSON]]   * [[transformations:parsejson|Parse JSON]]
-  * [[syntax:functions:isjson|isjson]] +  * [[syntax:functions:isjson|Functions:  IsJson()]]
  
transformations/constructjson.1578776113.txt.gz · Last modified: 2020/01/11 15:55 by dmitry

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki