Challenge #14: Convert nested flat records to JSON

Data Challenges
#1

For this challenge we have an input file that contains customer invoices including line items. The input file contains a variety of record types.

Input Data

C|Acme Inc
N|982
I|MLC|27|90.34
I|EAD|23|10.32
I|BBL|4|9.99
N|989
I|QTA|2|10.00
I|EAD|3|4.35
C|Dumbo Enterprises
C|Wayn Corp
N|1034
I|QTA|7|70.12
I|BBL|2|19.99
N|1038
I|QTA|8|80.44
C|Como Group

The following record types are given:

  • C - Represents a customer - customer name is delimited with |
  • N - Represents an invoice - invoice number is delimited with |
  • I - Represents an invoice line item - sku, quantity, and price are delimited with |

Each I record belongs to the first N record preceding it.
Each N record belongs to the first C record preceding it.

There are C records for which there are no invoices at present: see Dumbo Enterprises, and Como Group.

Requirements

We’re given the requirement to save the input information as a JSON structure. We need to save an array of customers, each with an array of invoices, which in turn each contains an array of line items.

If a customer does not have invoices, their invoices array should be empty.

Here’s the desired output format:

[
  {
    "type": "customer",
    "name": "Acme Inc",
    "invoices": [
      {
        "type": "invoice",
        "nr": 982,
        "line_items": [
          {
            "type": "invoice_item",
            "sku": "MLC",
            "count": 27,
            "price": 90.34
          },
          // ... additional line items...
        ],
      },
      // ... additional invoices ...
    ],
  },
  {
    "type": "customer",
    "name": "Dumbo Enterprises",
    "invoices": []
  },
  // ... additional customers ...
  
]

Starting Ooint

Use nested-flat-records-start.dfl (10.7 KB) to get started:

Screenshot 2020-08-03 at 16.01.50
Screenshot 2020-08-03 at 16.01.50348×659 24.9 KB

#2

The reference solution for this challenge nested-flat-records.dfl (53.5 KB) uses the following strategy:

  • keep track of parent records and assign parent records to children
  • collect child records into lists and place them inside their parent records

Here’s the flow in its entirety:

Screenshot 2020-08-20 at 10.54.46
Screenshot 2020-08-20 at 10.54.461236×507 37 KB

After the calc current customer and invoice step we reach the first milestone of the strategy: child records know about their parent records:

Screenshot 2020-08-20 at 10.53.38
Screenshot 2020-08-20 at 10.53.381296×394 98.5 KB

What follows is a sequence of collecting child records into lists and placing the child lists into parents.

Ultimately the flow constructs the following structure for each customer record:

{
  :type 'customer',
  :name 'Acme Inc',
  :invoices [
    {
      :type 'invoice'
      :nr 982,
      :line_items [
        {
          :price 90.34d,
          :count 27,
          :type 'invoice_item',
          :sku 'MLC'
        }, 
        # ... more line items
      ],
    }, 
    # ... more invoices
  ],
}

To finish up, the flow collects the top level customer records into a list as well. Converts that list into JSON and writes it to a file.