Constructing a JSON is easier than parsing. I’ve created a reusable module that converts any table (single-line or multi-line) into a valid JSON. With this module, constructing a JSON for the Magento endpoint you mentioned is a breeze. See below:
construct-json.morph (13.6 KB)
JSON_data.xlsx (11.8 KB)
Module: Main
In this module we start creating child JSON elements first. And then we simply append them into the parent JSON as columns. Table2json recognizes when a column value is a JSON element or JSON array, and treats it accordingly. Repeat as many times as many child-parent relationships are in the desired JSON.
The final dataset is converted into JSON again.
Module: Table2json
The module has 2 modes:
- Single-line tables are converted into a single JSON element, e.g.
{"Column1":100}. - Multi-line tables are converted into a JSON array, e.g.
[{"Column1":100}, {"Column1":200}]
EasyMorph data types are converted into JSON notation:
Text -> "Text"
Number -> 1234.34
Boolean -> true / false
Empty -> null
If a column value is already a JSON element or JSON array it remains as is. This allows nesting child JSON elements/arrays into a parent JSON element.
The input table must not have a column named “RowNo”.
When the module is opened first time, the “Input” action is empty. To populate it with data, right-click the action with desired output dataset, and choose “Send to Clipboard”. See here: Simplest way to copy data from datagrid to paste in input action
The result is a valid JSON that passes RFC 4627 validation in an online JSON formatter.
{
"entity":{
"extension_attributes":{
"gw_tax_amount":"string",
"gw_price":"string",
"gw_items_tax_amount":"string",
"gw_items_price":"string",
"gw_items_base_tax_amount":"string",
"gw_items_base_price":"string",
"gw_card_tax_amount":"string",
"gw_card_price":"string",
"gw_card_base_tax_amount":"string",
"gw_card_base_price":"string",
"gw_base_tax_amount":"string",
"gw_base_price":"string",
"gift_cards_amount":0,
"customer_balance_amount":0,
"base_gift_cards_amount":0,
"base_customer_balance_amount":0
},
"comments":[
{
"comment":"string",
"created_at":"string",
"entity_id":0,
"extension_attributes":{
},
"is_customer_notified":0,
"is_visible_on_front":0,
"parent_id":0
},
{
"comment":"string",
"created_at":"string",
"entity_id":1,
"extension_attributes":{
},
"is_customer_notified":0,
"is_visible_on_front":0,
"parent_id":0
}
],
"items":[
{
"additional_data":"string",
"base_cost":0,
"base_discount_amount":0,
"base_discount_tax_compensation_amount":0,
"base_price":0,
"base_price_incl_tax":0,
"base_row_total":0,
"base_row_total_incl_tax":0,
"base_tax_amount":0,
"description":"string",
"discount_amount":0,
"discount_tax_compensation_amount":0,
"entity_id":1,
"extension_attributes":{
},
"name":"string",
"order_item_id":0,
"parent_id":0,
"price":0,
"price_incl_tax":0,
"product_id":0,
"qty":0,
"row_total":0,
"row_total_incl_tax":0,
"sku":"string",
"tax_amount":0
},
{
"additional_data":"string",
"base_cost":0,
"base_discount_amount":0,
"base_discount_tax_compensation_amount":0,
"base_price":0,
"base_price_incl_tax":0,
"base_row_total":0,
"base_row_total_incl_tax":0,
"base_tax_amount":0,
"description":"string",
"discount_amount":0,
"discount_tax_compensation_amount":0,
"entity_id":0,
"extension_attributes":{
},
"name":"string",
"order_item_id":0,
"parent_id":0,
"price":0,
"price_incl_tax":0,
"product_id":0,
"qty":0,
"row_total":0,
"row_total_incl_tax":0,
"sku":"string",
"tax_amount":0
}
],
"updated_at":"string",
"transaction_id":"string",
"total_qty":0,
"tax_amount":0,
"subtotal_incl_tax":0,
"subtotal":0,
"store_to_order_rate":0,
"store_to_base_rate":0,
"store_id":0,
"store_currency_code":"string",
"state":0,
"shipping_tax_amount":0,
"shipping_incl_tax":0,
"shipping_discount_tax_compensation_amount":0,
"shipping_amount":0,
"shipping_address_id":0,
"order_id":0,
"order_currency_code":"string",
"is_used_for_refund":0,
"increment_id":"string",
"grand_total":0,
"global_currency_code":"string",
"entity_id":0,
"email_sent":0,
"discount_tax_compensation_amount":0,
"discount_description":"string",
"discount_amount":0,
"created_at":"string",
"can_void_flag":0,
"billing_address_id":0,
"base_total_refunded":0,
"base_to_order_rate":0,
"base_to_global_rate":0,
"base_tax_amount":0,
"base_subtotal_incl_tax":0,
"base_subtotal":0,
"base_shipping_tax_amount":0,
"base_shipping_incl_tax":0,
"base_shipping_discount_tax_compensation_amnt":0,
"base_shipping_amount":0,
"base_grand_total":0,
"base_discount_tax_compensation_amount":0,
"base_discount_amount":0,
"base_currency_code":"string"
}
}