Automation Professionals' Integration Toolkit Module

Automation Professionals is pleased to announce the immediately availability of a major upgrade to its free Simulation Aids Integration Toolkit module. The new feature justifying the "major upgrade" language is the implementation of eleven thirty-one new expression functions which use iteration to accomplish complex transforms of datasets and lists, to or from other datasets and lists, applying filtering, ordering, and computed new columns along the way. While there are certain operations that are still only available in the view() expression function, most of its functionality is now available without calling jython code.

Available for Ignition v8.1 only: v2.0.0.231640004

Visit my Module Sales page for the latest.

The list handling and generation code, including the handling of nested mappings, has no equivalent among the scripted functions, and is intended to accommodate the more complex data structures of Perspective. As with many of the technologies I implement, performance is the primary motivation for this work.

The online documentation has been updated in place, so both the module showcase and my Module Sales pages link to the latest content.

This functionality is also a stretch for the expression language foundation, as noted over here:

Paul's statement, only four days old, is no longer true. :grin:

There are debug loggers for the key iteration functions that report performance. Put "simaids" into the logger search box to get to them quickly.

If you would like a quick start to see these functions in action, I've been playing with this view:

View JSON
{
  "custom": {},
  "params": {},
  "props": {},
  "root": {
    "children": [
      {
        "children": [
          {
            "custom": {
              "filtered": [
                "Folsom",
                "Helsinki",
                "San Francisco",
                "Washington, DC",
                "Wellington",
                "Sydney",
                "Wellington"
              ],
              "filteredDS": {
                "$": [
                  "ds",
                  192,
                  1686616771960
                ],
                "$columns": [
                  {
                    "data": [
                      "Folsom",
                      "Helsinki",
                      "Jakarta",
                      "Madrid",
                      "Prague",
                      "San Diego",
                      "San Francisco",
                      "Shanghai",
                      "Tokyo",
                      "Washington, DC",
                      "Wellington",
                      "Delhi",
                      "Dhaka",
                      "Lagos",
                      "Karachi",
                      "Istanbul",
                      "Cairo",
                      "Mexico City",
                      "London",
                      "New York City",
                      "Tehran",
                      "Bogota",
                      "Rio de Janeiro",
                      "Riyadh",
                      "Singapore",
                      "Saint Petersburg",
                      "Sydney",
                      "Abidjan",
                      "Dar es Salaam",
                      "Wellington",
                      "Los Angeles",
                      "Berlin",
                      "Jeddah",
                      "Kabul",
                      "Mashhad",
                      "Milan",
                      "Kiev",
                      "Rome",
                      "Chicago",
                      "Osaka",
                      "Bandung",
                      "Managua",
                      "Paris",
                      "Shiraz",
                      "Manila",
                      "Montreal",
                      "Guadalajara",
                      "Dallas",
                      "Yerevan",
                      "Tunis"
                    ],
                    "name": "city",
                    "type": "String"
                  },
                  {
                    "data": [
                      "United States",
                      "Finland",
                      "Indonesia",
                      "Spain",
                      "Czech Republic",
                      "United States",
                      "United States",
                      "China",
                      "Japan",
                      "United States",
                      "New Zealand",
                      "India",
                      "Bangladesh",
                      "Nigeria",
                      "Pakistan",
                      "Turkey",
                      "Egypt",
                      "Mexico",
                      "United Kingdom",
                      "United States",
                      "Iran",
                      "Colombia",
                      "Brazil",
                      "Saudi Arabia",
                      "Singapore",
                      "Russia",
                      "Australia",
                      "Ivory Coast",
                      "Tanzania",
                      "New Zealand",
                      "United States",
                      "Germany",
                      "Saudi Arabia",
                      "Afghanistan",
                      "Iran",
                      "Italy",
                      "Ukraine",
                      "Italy",
                      "United States",
                      "Japan",
                      "Indonesia",
                      "Nicaragua",
                      "France",
                      "Iran",
                      "Philippines",
                      "Canada",
                      "Mexico",
                      "United States",
                      "Armenia",
                      "Tunisia"
                    ],
                    "name": "country",
                    "type": "String"
                  },
                  {
                    "data": [
                      77271,
                      635591,
                      10187595,
                      3233527,
                      1241664,
                      1406630,
                      884363,
                      24153000,
                      13617000,
                      658893,
                      405000,
                      11034555,
                      14399000,
                      16060303,
                      14910352,
                      14025000,
                      10230350,
                      8974724,
                      8825001,
                      8622698,
                      8154051,
                      7878783,
                      6429923,
                      5676621,
                      5535000,
                      5191690,
                      208374,
                      4765000,
                      4364541,
                      405000,
                      3884307,
                      3517424,
                      3456259,
                      3414100,
                      3001184,
                      1359905,
                      2908703,
                      2877215,
                      2695598,
                      2691742,
                      2575478,
                      2560789,
                      2229621,
                      1869001,
                      1780148,
                      1649519,
                      1495189,
                      1317929,
                      1060138,
                      1056247
                    ],
                    "name": "population",
                    "type": "int"
                  }
                ]
              },
              "rawData": [
                {
                  "city": {
                    "align": "center",
                    "editable": true,
                    "justify": "left",
                    "style": {
                      "backgroundColor": "#F7901D",
                      "classes": "some-class"
                    },
                    "value": "Folsom"
                  },
                  "country": "United States",
                  "population": 77271
                },
                {
                  "city": "Helsinki",
                  "country": "Finland",
                  "population": 635591
                },
                {
                  "city": "Jakarta",
                  "country": "Indonesia",
                  "population": 10187595
                },
                {
                  "city": "Madrid",
                  "country": "Spain",
                  "population": 3233527
                },
                {
                  "city": "Prague",
                  "country": "Czech Republic",
                  "population": 1241664
                },
                {
                  "city": "San Diego",
                  "country": "United States",
                  "population": 1406630
                },
                {
                  "city": "San Francisco",
                  "country": "United States",
                  "population": 884363
                },
                {
                  "city": "Shanghai",
                  "country": "China",
                  "population": 24153000
                },
                {
                  "city": "Tokyo",
                  "country": "Japan",
                  "population": 13617000
                },
                {
                  "city": "Washington, DC",
                  "country": "United States",
                  "population": 658893
                },
                {
                  "city": "Wellington",
                  "country": "New Zealand",
                  "population": 405000
                },
                {
                  "city": "Delhi",
                  "country": "India",
                  "population": 11034555
                },
                {
                  "city": "Dhaka",
                  "country": "Bangladesh",
                  "population": 14399000
                },
                {
                  "city": "Lagos",
                  "country": "Nigeria",
                  "population": 16060303
                },
                {
                  "city": "Karachi",
                  "country": "Pakistan",
                  "population": 14910352
                },
                {
                  "city": "Istanbul",
                  "country": "Turkey",
                  "population": 14025000
                },
                {
                  "city": "Cairo",
                  "country": "Egypt",
                  "population": 10230350
                },
                {
                  "city": "Mexico City",
                  "country": "Mexico",
                  "population": 8974724
                },
                {
                  "city": "London",
                  "country": "United Kingdom",
                  "population": 8825001
                },
                {
                  "city": "New York City",
                  "country": "United States",
                  "population": 8622698
                },
                {
                  "city": "Tehran",
                  "country": "Iran",
                  "population": 8154051
                },
                {
                  "city": "Bogota",
                  "country": "Colombia",
                  "population": 7878783
                },
                {
                  "city": "Rio de Janeiro",
                  "country": "Brazil",
                  "population": 6429923
                },
                {
                  "city": "Riyadh",
                  "country": "Saudi Arabia",
                  "population": 5676621
                },
                {
                  "city": "Singapore",
                  "country": "Singapore",
                  "population": 5535000
                },
                {
                  "city": "Saint Petersburg",
                  "country": "Russia",
                  "population": 5191690
                },
                {
                  "city": "Sydney",
                  "country": "Australia",
                  "population": 208374
                },
                {
                  "city": "Abidjan",
                  "country": "Ivory Coast",
                  "population": 4765000
                },
                {
                  "city": "Dar es Salaam",
                  "country": "Tanzania",
                  "population": 4364541
                },
                {
                  "city": "Wellington",
                  "country": "New Zealand",
                  "population": 405000
                },
                {
                  "city": "Los Angeles",
                  "country": "United States",
                  "population": 3884307
                },
                {
                  "city": "Berlin",
                  "country": "Germany",
                  "population": 3517424
                },
                {
                  "city": "Jeddah",
                  "country": "Saudi Arabia",
                  "population": 3456259
                },
                {
                  "city": "Kabul",
                  "country": "Afghanistan",
                  "population": 3414100
                },
                {
                  "city": "Mashhad",
                  "country": "Iran",
                  "population": 3001184
                },
                {
                  "city": "Milan",
                  "country": "Italy",
                  "population": 1359905
                },
                {
                  "city": "Kiev",
                  "country": "Ukraine",
                  "population": 2908703
                },
                {
                  "city": "Rome",
                  "country": "Italy",
                  "population": 2877215
                },
                {
                  "city": "Chicago",
                  "country": "United States",
                  "population": 2695598
                },
                {
                  "city": "Osaka",
                  "country": "Japan",
                  "population": 2691742
                },
                {
                  "city": "Bandung",
                  "country": "Indonesia",
                  "population": 2575478
                },
                {
                  "city": "Managua",
                  "country": "Nicaragua",
                  "population": 2560789
                },
                {
                  "city": "Paris",
                  "country": "France",
                  "population": 2229621
                },
                {
                  "city": "Shiraz",
                  "country": "Iran",
                  "population": 1869001
                },
                {
                  "city": "Manila",
                  "country": "Philippines",
                  "population": 1780148
                },
                {
                  "city": "Montreal",
                  "country": "Canada",
                  "population": 1649519
                },
                {
                  "city": "Guadalajara",
                  "country": "Mexico",
                  "population": 1495189
                },
                {
                  "city": "Dallas",
                  "country": "United States",
                  "population": 1317929
                },
                {
                  "city": "Yerevan",
                  "country": "Armenia",
                  "population": 1060138
                },
                {
                  "city": "Tunis",
                  "country": "Tunisia",
                  "population": 1056247
                }
              ]
            },
            "meta": {
              "name": "Table1"
            },
            "position": {
              "basis": "400px",
              "grow": 1
            },
            "propConfig": {
              "custom.constList": {
                "binding": {
                  "config": {
                    "expression": "asList(\u0027Hello\u0027, \"Cruel\", \"World\", 56, \"Today\")"
                  },
                  "type": "expr"
                }
              },
              "custom.constMap": {
                "binding": {
                  "config": {
                    "expression": "asMap(\u0027key1\u0027, \u0027Value1\u0027, \"key2\", \"Another\", \u0027key3\u0027, \u0027Cruel\u0027, \u0027key4\u0027, \u0027World\u0027)"
                  },
                  "type": "expr"
                }
              },
              "custom.filtered": {
                "access": "PRIVATE",
                "binding": {
                  "config": {
                    "expression": "forEach({this.props.data}, if(it()[\u0027population\u0027] \u003c 1000000, unMap(it()[\u0027city\u0027], \u0027value\u0027), null))"
                  },
                  "previewEnabled": false,
                  "type": "expr"
                },
                "persistent": true
              },
              "custom.filteredDS": {
                "access": "PRIVATE",
                "binding": {
                  "config": {
                    "expression": "unionAll(\n\tasList(\u0027city\u0027, \u0027country\u0027, \u0027population\u0027),\n\tasList(\u0027str\u0027, \u0027str\u0027, \u0027i\u0027),\n\tforEach(\n\t\t{this.props.data},\n\t\tasList(unMap(it()[\u0027city\u0027], \u0027value\u0027), it()[\u0027country\u0027], it()[\u0027population\u0027])\n\t)\n)"
                  },
                  "type": "expr"
                },
                "persistent": true
              },
              "custom.rawData": {
                "access": "PRIVATE"
              },
              "props.data": {
                "binding": {
                  "config": {
                    "expression": "forEach(\n\t{this.custom.filteredDS},\n\tasMap(it())\n)"
                  },
                  "type": "expr"
                }
              }
            },
            "type": "ia.display.table"
          }
        ],
        "meta": {
          "name": "FlexContainer"
        },
        "position": {
          "basis": "34px",
          "grow": 1
        },
        "props": {
          "direction": "column"
        },
        "type": "ia.container.flex"
      }
    ],
    "meta": {
      "name": "root"
    },
    "props": {
      "direction": "column"
    },
    "type": "ia.container.flex"
  }
}

Note: the new stuff is only lightly tested. The changes were sufficiently non-invasive that I consider the balance of the module to still be production-grade.

Edit: Name changed with v2.0.14 release.

14 Likes

I suppose I should note what isn't present, what might be present later, and what is not likely to ever work:

  • GROUP BY

I have a rough idea how to do this part, but haven't nailed the details down. Implemented in v2.0.3.

  • PIVOT

Depends on GROUP BY, and some dynamic column naming. Details to be worked out.

  • HAVING

Not actually necessary as a separate function, as it can be accomplished by stacking the where() outside the future grouping function.

  • LIMIT

Not necessary, as it can be accomplished by strategic placement of where() that checks idx().

  • JOIN (in various flavors)

There are ideas floating around.... Implemented in v2.0.6.

  • Tag reads (inside the iterator)

This probably will never work. The iteration functions rely on thread locals to carry state, and therefore must execute to completion. No callouts to the tag subsystem permitted. Even if that could be surmounted, I suspect the path-relative parsing support cannot tolerate my "pre-execution" methods.

If you are converting lists of tagpaths to tag values in transforms, I cannot help you. (And I think that is an anti-pattern--you should be indirect binding within a column view to display live tag values in tables, or indirect binding in templates in a template repeater.) Implemented tags() in v2.0.12. Indirection through tables or repeaters is unbearably slow.

Edit: Marked the items above that are no longer "future" features.

1 Like

So I was poking around and realized I missed a major feature: a substitute for python's range() functions.

And while at it, I factored out some common code so that where() and orderBy() could handle and return sequences instead of just datasets. Of the four iterators, now only the selectStart() function requires and returns a dataset. forEach() still only returns a list, but can accept datasets, lists, arrays, and single integers (for range behavior).

Here ya go:

For Ignition v8.1+ only: v2.0.1.231651531

8 Likes

This summary of performance advice by @lrose is relevant here:

Note bullet point #3. That particular question applies to transforms as well as property change events.

I have spent my holiday poking at this. Wanted to provide an efficient replacement for a common use of python transforms: Make a (Perspective) table that dynamically populates both .data and .columns from source dataset that isn't constrained to any particular structure or data types.

In Vision, one can achieve this (mostly) by pre-populating the column attributes dataset with all of the possible column names your application might deliver. No such thing in Perspective. But a transform can take the column names and types from a dataset, perform lookups on one or the other in a map of presets for .columns, and construct the corresponding entry.

I realized pretty quickly that I forgot to provide a way to iterate over the column information of a dataset, so there's now a columnsOf() expression to obtain that, and I realized that iterating over maps (as key-value pairs) is pretty handy, too. So that has been added to the iterators, and the asList() and asMap() functions have been updated to convert back and forth.

And I realized that I had no way to compare the performance of expression functions versus similar scripting solutions. I can't instrument the machinery of script transforms themselves, but I was able to instrument calls to runScript() as if calling a script transform. Should have close to the same jython overhead.

Anyways, I created a test case view for a "chameleon" table--takes any dataset tag via a tagpath parameter, and displays it with the simplest possible jsonifcation into .data and proof-of-concept for map-lookup generation of .columns. This is the library script that supports the python transforms:

from java.lang import Throwable
from com.inductiveautomation.ignition.common.xmlserialization import ClassNameResolver
from com.inductiveautomation.ignition.common.model.values import QualifiedValue
logger = system.util.getLogger('transforms')

resolver = ClassNameResolver.createBasic()

# Convert dataset to list of dictionaries for Perspective table data property.
def dsToJsonData(ds):
	headings = list(ds.columnNames)
	pyds = system.dataset.toPyDataSet(ds)
	return [dict(zip(headings, row)) for row in pyds]

def columnsOf(ds):
	return zip(ds.columnNames, map(resolver.getName, ds.columnTypes))

def deQualify(value):
	if isinstance(value, QualifiedValue):
		return value.value
	return value

def unMap(value, *args):
	value = deQualify(value)
	if isinstance(value, dict):
		for key in args:
			try:
				return deQualify(value[key])
			except:
				pass
	return value	

# Convert dataset columns to list of dictionaries for Perspective table columns property.
def dsToJsonColumns(ds, handlingMap):
	headings = list(ds.columnNames)
	try:
		return [dict(field=colName, header=dict(title=unMap(deQualify(handlingMap.get(colName, colName)), 'title'))) for colName in headings]
	except Throwable, t:
		logger.info("dsToJsonColumns() unexpected error", t)
	except Exception, e:
		logger.info("dsToJsonColumns() unexpected exception", shared.later.PythonAsJavaException(e))

The resolver is there for later decision-making based on column types, without having to deal with actual class instances.

Anyways, the bindings that call into that script module look like this:

timeMe('chameleon-pydata', 
runScript('transforms.dsToJsonData', 0, {view.custom.dataset}))

and:

timeMe('chameleon-pycolumns',
runScript('transforms.dsToJsonColumns', 0, {view.custom.dataset}, {this.custom.columnHandling}))

The pure expression bindings that perform the same transformation using my new iterators look like this:

timeMe('chameleon-data',
	forEach(
		{view.custom.dataset},
		asMap(it())
	)
)

and:

timeMe('chameleon-columns',
	forEach(
		columnsOf({view.custom.dataset}),
		asMap('field', it()[0],
			'header', asMap('title', try(unMap({this.custom.columnHandling}[it()[0]], 'title'), it()[0]))
		)
	)
)

The python columns transform has a slight advantage in that it is not converting the column type class instances to their string names (or abbreviations), which the columnsOf() expression function does unconditionally. So the speed advantage is a bit understated in these results from a little while ago:

[20:20:27]: Duration 279070ns timeme=chameleon-data, target=props.data
[20:20:27]: Duration 1439812ns timeme=chameleon-pydata, target=custom.pyData

[20:20:27]: Duration 372097ns timeme=chameleon-columns, target=props.columns
[20:20:27]: Duration 755441ns timeme=chameleon-pycolumns, target=custom.pyColumns

[20:20:27]: Duration 242858ns timeme=chameleon-data, target=props.data
[20:20:27]: Duration 1213344ns timeme=chameleon-pydata, target=custom.pyData

[20:20:27]: Duration 231521ns timeme=chameleon-columns, target=props.columns
[20:20:27]: Duration 773343ns timeme=chameleon-pycolumns, target=custom.pyColumns

[20:20:47]: Duration 298823ns timeme=chameleon-data, target=props.data
[20:20:47]: Duration 1316618ns timeme=chameleon-pydata, target=custom.pyData

[20:20:47]: Duration 295162ns timeme=chameleon-columns, target=props.columns
[20:20:47]: Duration 707748ns timeme=chameleon-pycolumns, target=custom.pyColumns

[20:20:47]: Duration 247223ns timeme=chameleon-data, target=props.data
[20:20:47]: Duration 1050020ns timeme=chameleon-pydata, target=custom.pyData

[20:20:47]: Duration 265910ns timeme=chameleon-columns, target=props.columns
[20:20:47]: Duration 619060ns timeme=chameleon-pycolumns, target=custom.pyColumns

The new Simulation Aids expressions are 2.5x to 5x faster than the python transforms, working with a 50Rx3C source dataset.

Latest for Ignition v8.1.x: v2.0.2.231701910

I think there are a few more tweaks to come. :grin:

10 Likes

This is awesome. Question - I currently have your old Simulation Aids installed on a production gateway and I mostly use it for the view() function - if I upgrade will I also see a 2.5x-5x speed increase for these expressions?

No. The view() function is unchanged, except that it now honors the debugMe() and traceMe() tools.

And it can't really ever be changed, because its syntax is built around python expressions. I can't evaluate those except with python, so :man_shrugging:

You will get the performance gains as you phase out script-based expressions and script transforms in favor of these pure expression techniques.

2 Likes

Hey Phil,

I'm a big fan of your view expression and and am currently trying to wrap my head around these new expressions.

Is it possible to do nested forEach statements?

I am using a Tree Component and am trying to see if it's possible to build the components required items list from a source dataset using your new expressions

Source Dataset Example
Col A Col B Col C
Item 1 Child1 Grandchild 1
Item 1 Child1 Grandchild 2
Item 2 Child of Item 2 Different Grandchild
Expected output
[
  {
    "label": "Item 1",
    "expanded": true,
    "data": [],
    "items": [
      {
        "label": "Child 1",
        "expanded": false,
        "data": [],
        "items": [
          {
            "label": "Grandchild 1",
            "expanded": false,
            "data": [],
            "items": []
          },
          {
            "label": "Grandchild 2",
            "expanded": false,
            "data": [],
            "items": []
          }
        ]
      }
    ]
  },
  {
    "label": "Item 2",
    "expanded": false,
    "data": [],
    "items": [
      {
        "label": "Child of Item 2",
        "expanded": false,
        "items": [
          {
            "label": "Different Grandchild",
            "expanded": false,
            "items": []
          }
        ]
      }
    ]
  }
]

I've been playing around with it for a bit and I can easily get a single forEach expression to work, but I'm not sure how I'd pass Col 1's value to the nested selectStar / where / forEach's it expression

Is this currently possible with the built in expressions? I'm assuming the lack of a groupBy will throw a wrench into my plans.

Thank you for your time and thanks again for this free module. It honestly has saved me countless hours!

Yes, but not recursively. If you share a bigger example of your source data, the view() expression you are currently using, and the output shape, I can help produce the necessary expression.

Do read the docs for the optional argument to the it() and idx() functions.

So I completely glossed over that part of the docs :roll_eyes: Apologies for not doing a btter job to RTFM

Input Data
"#NAMES"
"RouteName","StopName","WellName"
"#TYPES"
"str","str","str"
"#ROWS","71"
"Route 1","Stop 1","Well 1"
"Route 1","Stop 1","Well 2"
"Route 1","Stop 1","Well 3"
"Route 1","Stop 1","Well 4"
"Route 1","Stop 1","Well 5"
"Route 1","Stop 1","Well 6"
"Route 1","Stop 1","Well 7"
"Route 1","Stop 1","Well 8"
"Route 1","Stop 1","Well 9"
"Route 1","Stop 2","Well 1"
"Route 1","Stop 2","Well 2"
"Route 1","Stop 2","Well 3"
"Route 1","Stop 2","Well 4"
"Route 1","Stop 2","Well 5"
"Route 1","Stop 2","Well 6"
"Route 1","Stop 2","Well 7"
"Route 2","Stop 1","Well 1"
"Route 2","Stop 1","Well 2"
"Route 2","Stop 1","Well 3"
"Route 2","Stop 1","Well 4"
"Route 2","Stop 1","Well 5"
"Route 2","Stop 1","Well 6"
"Route 2","Stop 1","Well 7"
"Route 2","Stop 2","Well 1"
"Route 2","Stop 2","Well 2"
"Route 2","Stop 2","Well 3"
"Route 2","Stop 2","Well 4"
"Route 2","Stop 2","Well 5"
"Route 2","Stop 2","Well 6"
"Route 2","Stop 2","Well 7"
"Route 2","Stop 2","Well 8"
"Route 2","Stop 3","Well 1"
"Route 2","Stop 3","Well 2"
"Route 2","Stop 3","Well 3"
"Route 2","Stop 3","Well 4"
"Route 2","Stop 3","Well 5"
"Route 2","Stop 3","Well 6"
"Route 2","Stop 3","Well 7"
"Route 2","Stop 3","Well 8"
"Route 2","Stop 3","Well 9"
"Route 2","Stop 4","Well 1"
"Route 2","Stop 4","Well 2"
"Route 2","Stop 4","Well 3"
"Route 2","Stop 4","Well 4"
"Route 2","Stop 4","Well 5"
"Route 3","Stop 1","Well 1"
"Route 3","Stop 1","Well 2"
"Route 3","Stop 1","Well 3"
"Route 3","Stop 1","Well 4"
"Route 3","Stop 1","Well 5"
"Route 3","Stop 1","Well 6"
"Route 3","Stop 1","Well 7"
"Route 3","Stop 2","Well 1"
"Route 3","Stop 2","Well 2"
"Route 3","Stop 2","Well 3"
"Route 3","Stop 2","Well 4"
"Route 3","Stop 2","Well 5"
"Route 3","Stop 2","Well 6"
"Route 3","Stop 2","Well 7"
"Route 3","Stop 2","Well 8"
"Route 3","Stop 2","Well 9"
"Route 3","Stop 2","Well 10"
"Route 3","Stop 3","Well 1"
"Route 3","Stop 3","Well 2"
"Route 3","Stop 3","Well 3"
"Route 3","Stop 3","Well 4"
"Route 3","Stop 3","Well 5"
"Route 3","Stop 3","Well 6"
"Route 3","Stop 3","Well 7"
"Route 3","Stop 3","Well 8"
"Route 3","Stop 3","Well 9"
Desired output
[
  {
    "expanded": false,
    "label": "Route 1",
    "items": [
      {
        "expanded": false,
        "label": "Stop 1",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 8",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 9",
            "items": []
          }
        ]
      },
      {
        "expanded": false,
        "label": "Stop 2",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          }
        ]
      }
    ]
  },
  {
    "expanded": false,
    "label": "Route 2",
    "items": [
      {
        "expanded": false,
        "label": "Stop 1",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          }
        ]
      },
      {
        "expanded": false,
        "label": "Stop 2",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 8",
            "items": []
          }
        ]
      },
      {
        "expanded": false,
        "label": "Stop 3",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 8",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 9",
            "items": []
          }
        ]
      },
      {
        "expanded": false,
        "label": "Stop 4",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          }
        ]
      }
    ]
  },
  {
    "expanded": false,
    "label": "Route 3",
    "items": [
      {
        "expanded": false,
        "label": "Stop 1",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          }
        ]
      },
      {
        "expanded": false,
        "label": "Stop 2",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 8",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 9",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 10",
            "items": []
          }
        ]
      },
      {
        "expanded": false,
        "label": "Stop 3",
        "items": [
          {
            "expanded": false,
            "label": "Well 1",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 2",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 3",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 4",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 5",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 6",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 7",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 8",
            "items": []
          },
          {
            "expanded": false,
            "label": "Well 9",
            "items": []
          }
        ]
      }
    ]
  }
]

Current expression
forEach(
	{this.custom.routes},
	asMap(
		'label', it(0)['RouteName'],
		'items', forEach(
			it(0),
			if(
				it(0)['RouteName'] = it(1)['RouteName'],
				asMap(
					'label', it(1)['StopName'],
					'items', forEach(
						it(1),
						if(
							it(1)['RouteName'] = it(2)['RouteName'] && it(1)['StopName'] = it(2)['StopName'],
							asMap(
								'label', it(2)['WellName']
							),
							null
						)
					)
				),
				null
			)
		)
	)
)
Expression output
[
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 8"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 9"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 1",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 8"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 8"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 9"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 4",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 4",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 4",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 4",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "items": [
      {
        "label": "Stop 4",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 1",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 8"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 9"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 2",
        "items": [
          {
            "label": "Well 10"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 1"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 2"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 3"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 4"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 5"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 6"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 7"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 8"
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "items": [
      {
        "label": "Stop 3",
        "items": [
          {
            "label": "Well 9"
          }
        ]
      }
    ]
  }
]

I'm trying to pass the results of one iterator into next as using it as the first argument of the forEach expression. I'm not sure if that's kosher or not. As you can see from the output it's creating an equal number of items to the source input instead of grouping the objects together by level

Hmm. You need groupBy(), which I haven't written yet. :slightly_frowning_face:

1 Like

LOL well i guess I chose the wrong test candidate then :sweat_smile:

I will try on something that doesn't require grouping.

I will say, so far I like the way everything chains together. If you stare at it long enough it makes sense what it's trying to do

1 Like

No longer true. :grin:

For Ignition v8.1+: v2.0.3.231942018

With @Matthew.gaitan 's input dataset in a custom property named source, this expression:

forEach(
	asMap(
		groupBy(
			{this.custom.source},
			it()['RouteName'],
			it()['StopName']
		)
	),
	asMap(
		'label', it()[0],
		'expanded', false,
		'data', asMap('nodeType', 'Route'),
		'items', forEach(
			it()[1],
			asMap(
				'label', it()[0],
				'expanded', false,
				'data', asMap('nodeType', 'Stop'),
				'items', forEach(
					it()[1],
					asMap(
						'label', it()['WellName'],
						'expanded', false,
						'data', asMap('nodeType', 'Well'),
						'items', asList()
					)
				)
			)
		)
	)
)

Yields this output:

[
  {
    "label": "Route 1",
    "expanded": true,
    "data": {
      "nodeType": "Route"
    },
    "items": [
      {
        "label": "Stop 1",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 2",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "expanded": true,
    "data": {
      "nodeType": "Route"
    },
    "items": [
      {
        "label": "Stop 1",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 2",
        "expanded": true,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 3",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 4",
        "expanded": true,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "expanded": true,
    "data": {
      "nodeType": "Route"
    },
    "items": [
      {
        "label": "Stop 1",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 2",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 10",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 3",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      }
    ]
  }
]

The magic is here:

https://www.automation-pros.com/simaids/doc/expression.groupby.html

The asMap() function was altered to handle multi-item keys in a sequence of key-value pairs as requests to make nested mappings. Note that the asMap function will work with key values that are not valid names within Perspective objects, so you may need to construct such maps nested in the expression that consumes them (as in this case).

Both asMap() and asList() were updated to properly return an empty object when given no arguments.

3 Likes

Note that expressions don't have any mechanism to express recursion, so this approach can only produce fixed-depth trees.

I don't know whether to be impressed or terrified :slight_smile:

Very cool stuff.

8 Likes

I'm pretty sure I can use the mappings of groups to implement dataset joins. :sweat_smile:

I tacked on a timeMe(), which yielded 2.45-2.51 milliseconds for Matthew's source data. Jython doesn't even get started that fast.

8 Likes

Phil, this is freaking awesome! Thank you! Going to have to upgrade asap :smiley:

Of course, the general purpose of a GROUP BY clause is to apply aggregate functions to the groups. A simple example is to make a simple view with two tables side by side. Name the first one Source and leave its sample data in place. In the second one, place this expression binding on props.data:

orderBy(
	forEach(
		groupBy({../Source.props.data}, it()['country']),
		asMap(
			'country', it()[0],
			'cities', len(it()[1]),
			'population', sum(
				forEach(
					it()[1],
					it()['population']
				)
			)
		)
	),
	descending(it()['population'])
)

You get this output:

[
  {
    "country": "China",
    "cities": 1,
    "population": 24153000
  },
  {
    "country": "United States",
    "cities": 8,
    "population": 19547689
  },
  {
    "country": "Japan",
    "cities": 2,
    "population": 16308742
  },
  {
    "country": "Nigeria",
    "cities": 1,
    "population": 16060303
  },
  {
    "country": "Pakistan",
    "cities": 1,
    "population": 14910352
  },
  {
    "country": "Bangladesh",
    "cities": 1,
    "population": 14399000
  },
  {
    "country": "Turkey",
    "cities": 1,
    "population": 14025000
  },
  {
    "country": "Iran",
    "cities": 3,
    "population": 13024236
  },
  {
    "country": "Indonesia",
    "cities": 2,
    "population": 12763073
  },
  {
    "country": "India",
    "cities": 1,
    "population": 11034555
  },
  {
    "country": "Mexico",
    "cities": 2,
    "population": 10469913
  },
  {
    "country": "Egypt",
    "cities": 1,
    "population": 10230350
  },
  {
    "country": "Saudi Arabia",
    "cities": 2,
    "population": 9132880
  },
  {
    "country": "United Kingdom",
    "cities": 1,
    "population": 8825001
  },
  {
    "country": "Colombia",
    "cities": 1,
    "population": 7878783
  },
  {
    "country": "Brazil",
    "cities": 1,
    "population": 6429923
  },
  {
    "country": "Singapore",
    "cities": 1,
    "population": 5535000
  },
  {
    "country": "Russia",
    "cities": 1,
    "population": 5191690
  },
  {
    "country": "Ivory Coast",
    "cities": 1,
    "population": 4765000
  },
  {
    "country": "Tanzania",
    "cities": 1,
    "population": 4364541
  },
  {
    "country": "Italy",
    "cities": 2,
    "population": 4237120
  },
  {
    "country": "Germany",
    "cities": 1,
    "population": 3517424
  },
  {
    "country": "Afghanistan",
    "cities": 1,
    "population": 3414100
  },
  {
    "country": "Spain",
    "cities": 1,
    "population": 3233527
  },
  {
    "country": "Ukraine",
    "cities": 1,
    "population": 2908703
  },
  {
    "country": "Nicaragua",
    "cities": 1,
    "population": 2560789
  },
  {
    "country": "France",
    "cities": 1,
    "population": 2229621
  },
  {
    "country": "Philippines",
    "cities": 1,
    "population": 1780148
  },
  {
    "country": "Canada",
    "cities": 1,
    "population": 1649519
  },
  {
    "country": "Czech Republic",
    "cities": 1,
    "population": 1241664
  },
  {
    "country": "Armenia",
    "cities": 1,
    "population": 1060138
  },
  {
    "country": "Tunisia",
    "cities": 1,
    "population": 1056247
  },
  {
    "country": "New Zealand",
    "cities": 2,
    "population": 810000
  },
  {
    "country": "Finland",
    "cities": 1,
    "population": 635591
  },
  {
    "country": "Australia",
    "cities": 1,
    "population": 208374
  }
]

Since the data is in JSON format, not dataset format, there must be a forEach() inside the aggregate function to pull the desired data out of the map. If the source data to groupBy() is a dataset, then the value of it()[1] when iterating through the groups will also be a dataset, and you would use sum(it()[1], 'population') instead of generating the sequence for sum(). Like so:

orderBy(
	forEach(
		groupBy({../Source.props.data}, it()['country']),
		asMap(
			'country', it()[0],
			'cities', len(it()[1]),
			'population', sum(it()[1], 'population')
		)
	),
	descending(it()['population'])
)

Also, found a bug in orderBy(). Link fixed above.

I should emphasize that when you look at these iterators, remember that the first argument, the source, is evaluated completely before that function starts its own loop. So if you see it() or idx() in that position, it is referencing an outer iteration loop.

{ timeMe() says that first expression runs in 1.7-2.2 milliseconds. }

Phil, I'm having difficulty developing a view() expression to pivot a dataset. There are also some other things I would like to accomplish at the same time, but currently I'm stuck on getting the correct Pivoted output.

Original dataset is simply a wide result for system.tag.queryTagHistory(). Preferably the Column Headings would be the Hours from the t_stamp column.

I have read the documentation, but I just can't seem to put the correct expression together.

This then leads to other questions:

  1. Is it possible to combine two of the columns into one the value would be the sum of the two columns per row.
    2.Is it possible to supply an order by expression to order a column outside of just the normal ascending or descending. For instance our fiscal day runs from 7am - 7am, so ideally the columns after pivoting would be in that order, where the hours that have not yet occurred on the current day are filled by the previous day's numbers?

TYIA for any assistance.