Example: Constructing JSON

Moved from Parsing JSON/XML from web responses

Hey @dgudkov -
Related but perhaps off topic.

I just wanted to open this up to see if others have a tough time with this. I struggle with “Constructing” the JSON files more than anything, especially when it has depth to it. Here is an example:

Again, I’ve been recently working on Magento so I’ll keep it relevant to that:
https://devdocs.magento.com/swagger/#/salesInvoiceRepositoryV1/salesInvoiceRepositoryV1SavePost

In this example if you want to invoice the customer you need to define the product(s) and their qty’s in
POST /V1/invoices/

So the post is potentially different between each sales order based on the number of lines the sales order has. In this case the business user & ERP know that this order has (3) lines and its fully invoiced, but the next order only has (2) lines so if we know that information in a flat file, constructing it in JSON to me is a bit tricky.

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"
   }
}

***** moved from #lounge to #uncategorized

Here is an updated example that uses the “Construct JSON” action that became available starting from version 4.1. It’s simpler because it doesn’t require a separate module.

construct-json.morph (13.1 KB)