Strategy to Loop Over Table to Create New List of Objects - Each Containing a Sub-List

Viewed 63

As tables & spreadsheets don't deal very well with nested data, it's super common to break up an object into multiple lines when writing to a table or spreadsheet. An example using a cookbook recipe could be a recipe object containing an arrayList / collection of separate steps within it.

Example recipe object list (written in JSON as writing VBA objects is difficult since their names are stored in the modules, and this could apply to other languages like vb.net or C# as well):

{
    "Baked Potato": {
        "Steps": {
            "Wash Potato": {},
            "Bake Potato": {}
        }
    },
    "Cheese Quesadilla": {
        "Steps": {
            "Open Tortilla": {},
            "Add Cheese": {},
            "Microwave 30 seconds": {}
        }
    }
}

If I were looping over my "cookbook" list of recipes and printing them to a table, the output would look like this:

Recipe Steps
Baked Potato Wash Potato
Baked Potato Bake Potato
Cheese Quesadilla Open Tortilla
Cheese Quesadilla Add Cheese
Cheese Quesadilla Microwave 30 Seconds

While looping over the object is pretty straightforward, where I'm struggling is how do go about the reverse:

Question: How would I loop over each row in the table, and create a new recipe object only for unique recipes, and adding the correct steps to that recipe's step list? (I'm fine going from object to table, it's looping over a table and creating an object I'm having a harder time thinking of.)

My thoughts so far:

I've been leaning towards using an ArrayList instead of dictionary, as things may be nested multiple levels deep (like having list of ingredients) and a list of objects makes more sense to me mentally than the dictionary which I've struggled to really use yet..

I was thinking I would first need to get a unique list of just the recipe names in the table, then loop over that separate unique recipe name list and within that loop have a nested For Each loop for rows start to rows end, and within that loop a while loop that somehow only adds the nested ingredients while the recipe name of the current line matches the unique list?

RecipeList as ArrayList:

  • Baked Potato
  • Cheese Quesadilla

Pseudocode:

For Each recipe in RecipeList..

  New RecipeObject

  For Each row in RecipeTable..
    While (currentRow,RecipeColumn) in RecipeTable = current recipe in RecipeList
      RecipeObject.Name = (currentRow, recipeColumn)
      RecipeObject.Add (currentRow, stepsColumn)

This kind of feels like the right track, but maybe isn't the correct use of a while loop as it would break if the recipe lines weren't grouped together in the table. Maybe just a regular If would work better. I also don't want to create a new object with each table line, just one for each unique recipe but pulling out a separate unique list seems like maybe an added unnecessary and slower loop?

Thanks, and sorry didn't know how to phrase this well enough to find a duplicate question.

vba, vb.net, c# or even java examples all welcome as this is more of a strategy question than a language specific one, so I wasn't sure how to best tag this.

1 Answers

You can easily convert your JSON(a cookbook recipe) to table with esProc SPL(a java based open-source scripting language for data processing):

1、Parse the JSON into a multilevel table sequence

2、Create an empty table sequence

3、Travse the recipes,put the recipe and its Steps into the empty table that we made before(current Steps is a sequence)

4、get the table that you need

SPL code:

=json(file("data.json").read())
=create(Recipe,Steps)
>A1.fname().(~|[A1.field(#).Steps.fname()]).(A2.record(~))
=A2.news(Steps;Recipe,~:Steps)

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as json2table.splx and invoke it in a Java application as you call a stored procedure:

...
Class.forName("com.esproc.jdbc.InternalDriver");
con = DriverManager.getConnection("jdbc:esproc:local://");
st = con.prepareCall("call json2table.splx()");
ResultSet rs = st.getResultSet();;
...

with

<!-- https://mvnrepository.com/artifact/com.scudata.esproc/esproc -->
<dependency>
    <groupId>com.scudata.esproc</groupId>
    <artifactId>esproc</artifactId>
    <version>20220402</version>
</dependency>

You can also call an SPL script through the ODBC interface provided by esProc SPL.

Related