Dictionary Key Order

I have a view with a custom property which is running a named query and returning the data as JSON. Then I have another custom property which is referencing that JSON data and running a script transform to parse out only the columns that I need to show in a table. When I make my temp_dict, I put the ‘Title’ key first and the ‘Details’ key second. However, when the script builds out the new dictionary, it reverses this order (The ‘Details’ key shows up before the ‘Title’ key). How can I write the script to make the temp_dict maintain the key order that I enter?

def transform(self, value, quality, timestamp):
	# Ignition formats JSON in a list
	json_list = []
	
	# If there is valid data, filter and create a new JSON list
	if value:
		for index, json_item in enumerate(value):
			temp_dict = {
				'Title':json_item['cr_title'],
				'Details':json_item['change_request']
				}
			json_list.append(temp_dict)
	# Otherwise add an empty entry to maintain table visual layout
	else:
		temp_dict = {
			'Title':'',
			'Details':''
			}
		json_list.append(temp_dict)
	
	# Return fitered data
	return json_list

A few notes:

  1. In the interim, I have simply moved to using a dataset which I can order just fine. So I can get this to work. However, I’d still like to learn what I am doing wrong so I know for next time.
  2. When I build a dictionary in Visual Studio, the key order is maintained in the order that I entered them. Visual Studio is running Python 3.10. I am aware of a Python change somewhere between version 2 and 3 that gave some order to dictionaries, so this could likely be a cause of this. However, I don’t understand that well enough at this point to know that it is the root cause of the differences I am seeing in results between the two different software platforms.

You need OrderedDict.

https://docs.python.org/2.7/library/collections.html#collections.OrderedDict

4 Likes

Thanks Kevin - that is likely what I am looking for, but I am having an issue with it yet. In the script transform, it does not seem to perform as it does in the script console. It works as I would expect it to within the script console. See below.

I suspect Perspective’s JSON layer is using an unordered dictionary.

The answer for others with this specific problem is to define the table’s columns configuration, where each column is called out explicitly.

1 Like

Yeah, it looks like the ordering is lost after execution when the results get turned into a Java Map.

The columns configuration is the answer here, I didn’t read close enough to realize that.

No problem. I will adjust the table column configuration (or simply leave it as a dataset). Thanks for teaching me something along the way!

FWIW, the table columns should probably always be configured regardless to give control over how they’re displayed, otherwise it may produce unexpected results at times

4 Likes

Indeed, the implementation of dict changed in 3.6 (at least for Cpython ?). It made it keep keys in order, though it wasn't the goal - the new implementation focused on size and performance.
On the other hand, OrderedDict's goal is to keep keys ordered.
Here's what Raymond Hettinger (the new dict AND OrderedDict's father) has to say about it:
https://mail.python.org/pipermail/python-dev/2017-December/151266.html

Interesting dude, if you're interested about python.

As long as we're being pedantic: it is explicitly part of the language spec as of 3.7: What’s New In Python 3.7 — Python 3.12.1 documentation

It's all somewhat immaterial to Ignition, though; besides being on Jython 2.7, in this context we're talking about (technically) JSON-like mapping structures, which happen to closely resemble Python's dictionaries, but are explicitly order-agnostic.

FWIW I notice that ordering is lost when using toDict() method on JSON stored in a PyDocumentObjectAdapter (eg. in a Document memory tag in a UDT) to get a dictionary. You can however get the original JSON ordering using the keys() method prior to converting the JSON object to a dictionary with toDict().

Hi there.
I'm sorry, I know this is a bit old post, but I have a problem on this topic and I didn't want to open another post on the same topic
I've searched everywhere on the web, I can't find an example of how to configure the columns, as you answered,

"The columns configuration is the answer here"

do you have any suggestions?
thaks in advance.

They were referring to the columns configuration in the table.

https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table#PerspectiveTable-Properties

2 Likes

What's the issue exactly ?

Hey, ciao Pascal,
I'm sorry to bother you againg.
My problem isn't that big of a deal.
This code exports the records directly from the namedQuery (if they are not filtered), otherwise I only export the filtered records.
IF I don't have a filter applied the columns exported to Excel are OK, otherwise IF I have a filter the columns are exported in a different order. So far I've tried with "OrderedDict" but I can't sort them and I was almost thinking of leaving it as it is before you wrote to me.

def runAction(self, event):
	if self.getSibling("tblDeodorazione").props.filter.results.data:		
		def jsonToDataset(list):
			columns = dict(list[0]).keys()
			data = []
			for item in list:
				row = []
				for column in columns:
					cellValue = item.get(column, '')
					try:
						dict(cellValue)
						cellValue = cellValue['value']
					except:
						pass
					row.append(cellValue)
				data.append(row)
				
			return system.dataset.toDataSet(columns, data)
				
		json = self.getSibling("tblDeodorazione").props.filter.results.data
		data = jsonToDataset(json)
		Excel_xlsx = system.dataset.toExcel(True,[data])
		system.perspective.download("rptDeodorazione" + "(" + self.getSibling("tblDeodorazione").props.filter.text + ")" + ".xlsx",Excel_xlsx)
	else:
		parameters  = {"parStartDate":self.getSibling("startDate").props.value, "parEndDate":self.getSibling("endDate").props.value}
		data = system.db.runNamedQuery("UGP9/qryDeodorazione", parameters)	
		data = system.dataset.toExcel(True, [data])
		system.perspective.download("rptDeodorazione.xlsx",data)

There are a few things to fix there, I'll need some time top go over the code in more details.

Alright so first things first:

  • don't call things list. That's a python built-in.
  • you should define jsonToDataset in your project's library, not here.

Now if I understand correctly, your issue is that you lose the column order when using filtered data.
I'm not sure if this is the result of the jsonToDataset function or if it's already unordered before, but I believe the simplest solution might be to pass a list with the expected columns, in the expected order. You can get this from the table's columns property:

columns = [col.field for col in columns]

Then you only need to use this list to order your columns, either when you build the dataset, or afterward with (I believe) system.dataset.filterColumns.

I'm not rewriting the jsonToDataset function just yet, because I'm not sure about why the try is there for, but simply removing its first line and adding a columns parameter to pass the columns list as an argument should fix the issue.

1 Like

Wow, you're great!
I just replaced:

columns = dict(list[0]).keys()

with your suggestion:

columns = [col.field for col in self.getSibling("tblDeodorazione").props.columns]

and Voilà everything is perfect.

Merci beaucoup.