User Tools

Site Tools


transformations:constructjson

This is an old revision of the document!


Construct JSON

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.

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: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.

Nesting JSON objects

EasyMorph detects if a text value already is a JSON object or JSON array. In this case, such text value is inserted verbatim, without wrapping with 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. Field "Hiatus" is inserted as a JSON array, not as text too.

See also

transformations/constructjson.1578775446.txt.gz · Last modified: 2020/01/11 15:44 by dmitry

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki