transformations:constructjson
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
| transformations:constructjson [2020/01/11 20:55] – [Nesting JSON objects] dmitry | transformations:constructjson [2026/09/22 05:27] (current) – [Example #6] minor edit roberto | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ===== Construct | + | {{ transformations: |
| + | ======CONSTRUCT | ||
| + | Category: Transform / Web\\ | ||
| - | This action constructs a [[https:// | + | \\ |
| - | * Object per row, array from objects | + | =====Description===== |
| - | * Array per column | + | This action constructs a [[https:// |
| - | A constructed JSON object | + | \\ |
| + | =====Action settings===== | ||
| + | ^ Mode ^ Description | ||
| + | |Object per row, array from objects|Each row of the source dataset | ||
| + | |Array per column|The source dataset | ||
| - | ==== Mode " | + | \\ |
| - | In this mode, each row of the source dataset | + | ====" |
| + | ^ Setting | ||
| + | |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 | ||
| + | |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.| | ||
| - | ^ Name ^ Kingdom | + | \\ |
| - | | Rabbit | + | ====" |
| + | ^ Setting | ||
| + | |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' | ||
| - | is constructed | + | \\ |
| + | =====Remarks===== | ||
| + | ====Data type conversion==== | ||
| + | EasyMorph data types are converted to JSON types as follows: | ||
| + | |||
| + | ^ EasyMorph ^ Example ^ JSON ^ Example ^ | ||
| + | | Text | ABC | string | ||
| + | | Number | ||
| + | | Number (formatted as date) | ||
| + | | Boolean | ||
| + | | Empty | ||
| + | | 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 | ||
| + | | Circuit Gilles Villeneuve | ||
| + | |||
| + | would produce | ||
| + | |||
| + | < | ||
| + | { | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | }, | ||
| + | " | ||
| + | } | ||
| + | </ | ||
| + | Notice that " | ||
| + | |||
| + | \\ | ||
| + | =====Examples===== | ||
| + | ====Example #1==== | ||
| + | > | ||
| + | |||
| + | ===Before (source table)=== | ||
| + | ^ Name ^ Kingdom | ||
| + | | Rabbit |Animalia | Chordata | Mammalia | ||
| + | ===After (result table)=== | ||
| < | < | ||
| { | { | ||
| Line 20: | Line 80: | ||
| " | " | ||
| " | " | ||
| - | " | ||
| " | " | ||
| - | " | ||
| - | " | ||
| } | } | ||
| </ | </ | ||
| - | 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: | ||
| + | \\ | ||
| + | ====Example #2==== | ||
| + | >Create flat JSON objects (inside an array) 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)=== |
| < | < | ||
| [ | [ | ||
| Line 45: | Line 108: | ||
| } | } | ||
| ] | ] | ||
| - | | ||
| </ | </ | ||
| - | ==== 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: |
| + | \\ | ||
| + | ====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 ^ | ^ ID ^ Name ^ | ||
| - | | [1,2] | [Apple, | + | | 1 | Apple |
| + | | 2 | Orange | ||
| + | ===After (result table)=== | ||
| + | ^ ID ^ Name ^ | ||
| + | | [1,2] | [" | ||
| - | ==== Data type conversion ==== | + | ===Action parameters=== |
| - | EasyMorph data types are converted to JSON types as follows: | + | >Mode: Array per column |
| - | ^ EasyMorph ^ Example | + | \\ |
| - | | Text | ABC | string | + | ====Example |
| - | | Number | + | >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: {" |
| - | | Number | + | |
| - | | Boolean | + | |
| - | | Empty | + | |
| - | | Error | #Division by zero | Fails to convert | + | |
| - | Note that the action fails if the source | + | ===Before (source |
| + | ^ ID ^ Name ^ | ||
| + | | 1 | Apple | | ||
| - | ==== Nesting JSON objects ==== | + | ===After (result table)=== |
| - | 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 | + | ===Action parameters=== |
| - | | Circuit Gilles Villeneuve | + | >Mode: Object per row, array from objects |
| + | >Make an array even for single object: checked | ||
| - | would produce | + | \\ |
| + | ====Example #5==== | ||
| + | >Leave empty values out of the arrays. Without this option, the " | ||
| + | |||
| + | ===Before (source table)=== | ||
| + | ^ ID ^ Name ^ Discount ^ | ||
| + | | 1 | Apple | 10 | | ||
| + | | 2 | Orange | | | ||
| + | | 3 | Banana | 5 | | ||
| + | |||
| + | ===After (result table)=== | ||
| + | ^ ID ^ Name ^ Discount ^ | ||
| + | | [1,2,3] | [" | ||
| + | |||
| + | ===Action parameters=== | ||
| + | >Mode: Array per column | ||
| + | >Exclude nulls from arrays: checked | ||
| + | |||
| + | \\ | ||
| + | ====Example #6==== | ||
| + | > | ||
| + | |||
| + | ===Before (source table)=== | ||
| + | ^ Customer ^ Product ^ Qty ^ | ||
| + | | Alice | Apple | 3 | | ||
| + | | Alice | Orange | 1 | | ||
| + | | Bob | Banana | 2 | | ||
| + | |||
| + | ===After (result table)=== | ||
| + | Alice: | ||
| < | < | ||
| - | { | + | [ |
| - | | + | |
| - | "Country":" | + | "Product":" |
| - | " | + | "Qty":3 |
| - | " | + | |
| - | " | + | |
| - | " | + | |
| - | "long":-73.522461 | + | |
| }, | }, | ||
| - | "Hiatus": | + | |
| + | | ||
| + | " | ||
| + | } | ||
| + | ] | ||
| + | </ | ||
| + | |||
| + | Bob: | ||
| + | |||
| + | < | ||
| + | { | ||
| + | " | ||
| + | " | ||
| } | } | ||
| </ | </ | ||
| - | Notice that " | ||
| - | ==== See also ==== | + | ===Action parameters=== |
| - | * [[https:// | + | >Mode: Object per row, array from objects |
| - | * [[transformations: | + | >Group by selected columns: Customer |
| - | * [[syntax: | + | |
| + | \\ | ||
| + | ====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 | [" | ||
| + | | Bob | [" | ||
| + | |||
| + | ===Action parameters=== | ||
| + | >Mode: Array per column | ||
| + | >Group by selected columns: Customer | ||
| + | |||
| + | |||
| + | |||
| + | \\ | ||
| + | =====Community examples===== | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | |||
| + | \\ | ||
| + | ===== See also ===== | ||
| + | * [[transformations: | ||
| + | * [[syntax: | ||
transformations/constructjson.1578776113.txt.gz · Last modified: by dmitry