# jsonEncode dataset tags with Query as Value Source

**URL:** <https://forum.inductiveautomation.com/t/jsonencode-dataset-tags-with-query-as-value-source/58654>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [April 20, 2022, 11:09pm UTC](https://forum.inductiveautomation.com/t/jsonencode-dataset-tags-with-query-as-value-source/58654 "2022-04-20T23:09:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 20, 2022, 11:09pm UTC](https://forum.inductiveautomation.com/t/jsonencode-dataset-tags-with-query-as-value-source/58654/1 "2022-04-20T23:09:13Z")

</div>

One of my projects have a nested dictionary that contains report configuration and datasets.

I ran into this after upgrading to Ignition 8.1.15 from (I think) 8.1.7

When trying to encode a dictionary of datasets I get a max recursion error. I traced it down to datasets with a query value source. All other datasets work. As a temporary fix, I modified the dataset before encoding it. This happens when using a dataset from the runQuery function or from reading a tag derived from a query.

To replicate:

```auto
dsTest1 = system.db.runQuery('SELECT * FROM someTable')
print system.util.jsonEncode({'test' : dsTest1}, 4)

```

Temporary Fix:

```auto
dsTest2 = system.dataset.addColumn(dsTest1, [None for row in system.dataset.toPyDataSet(dsTest1)], 'value', str)
print system.util.jsonEncode({'test' : dsTest2}, 4)

```

Is an unintended result of some other code fix in a previous release?

---

<div class="post-metadata">

**Author:** ![plo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/plo/32/33875_2.png) [@plo](https://forum.inductiveautomation.com/u/plo)\
**Post date:** [April 22, 2022, 9:53pm UTC](https://forum.inductiveautomation.com/t/jsonencode-dataset-tags-with-query-as-value-source/58654/2 "2022-04-22T21:53:36Z")

</div>

Hello, are you able to set up and use a Named Query with system.db.runNamedQuery? From a quick test, there doesn’t seem to be an issue with Named Queries.  
[https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery)

System.db.runQuery() appears to return a  
**\<type ‘com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet’\>**  
type object, whereas system.db.runNamedQuery() returns a  
**\<type ‘com.inductiveautomation.ignition.common.BasicDataset’\>**.  
The two different dataset return types may be what is causing the max recursion error.

If you wanted investigate further with system.db.runQuery(), please open a Support Ticket so that we may investigate further. [Support Home Page | Inductive Automation](https://inductiveautomation.com/support/home/)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 23, 2022, 12:01pm UTC](https://forum.inductiveautomation.com/t/jsonencode-dataset-tags-with-query-as-value-source/58654/3 "2022-04-23T12:01:13Z")

</div>

Confirmed. I set up a query tag, and a dataset tag with the just the data hand-typed in. From what I’m seeing:

- All datasets will encode when used singly, but not necessarily when used as in a key/value pair.
- I could see this happening after the change to PyDataSet to make it more efficient. In the meantime, you can get the underlying dataset from a PyDataSet and it will encode ok, either by using the undelyingDataset property or system.dataset.toDataSet().
- The dataset from a query tag, even though it’s a BasicDataset, will not encode. addColumn() creates a new dataset.

```python
def encode(dataIn):
	try:
		return system.util.jsonEncode(dataIn)
	except:
		return 'Cannot encode'

query = 'SELECT 1 as x, 2 as y'

queryResult = system.db.runQuery(query, 'Production')
print ' ***PyDataSet***'
print '-print queryResult, type(queryResult)'
print queryResult, type(queryResult)
print '\n-encode(queryResult)'
encode(queryResult)

print "\n-print {'test' : queryResult}"
print {'test' : queryResult}
print "-encode({'test' : queryResult})"
encode({'test' : queryResult})

print '\n-type(queryResult.underlyingDataset)'
print type(queryResult.underlyingDataset)
print "\n-print {'test' : queryResult.underlyingDataset}"
print {'test' : queryResult.underlyingDataset}
print "\n- encode({'test' : queryResult.underlyingDataset})
encode({'test' : queryResult.underlyingDataset})

print '\n-print type(system.dataset.toDataSet(queryResult))'
print type(system.dataset.toDataSet(queryResult))
print "\n-print {'test': system.dataset.toDataSet(queryResult)}"
print {'test': system.dataset.toDataSet(queryResult)}
print "\n-encode({'test': system.dataset.toDataSet(queryResult)})"
encode({'test': system.dataset.toDataSet(queryResult)})

tagList = ['[Test]xyDatasetTag', '[Test]xyQueryTag']
xyDatasetTag, xyQueryTag = [tag.value for tag in system.tag.readBlocking(tagList)]
print '\n***Dataset tag (no query)***'
print 'print xyDatasetTag, type(xyDatasetTag)'
print xyDatasetTag, type(xyDatasetTag)
print '\n-encode(xyDatasetTag)'
encode(xyDatasetTag)
print "\n-{'test' : xyDatasetTag}"
print {'test' : xyDatasetTag}
print "\n-encode({'test' : xyDatasetTag})"
encode({'test' : xyDatasetTag})

print '\n*** Query tag'
print '\n-print xyQueryTag, type(xyQueryTag)'
print xyQueryTag, type(xyQueryTag)
encode(xyQueryTag)
print "\n-print {'test' : xyQueryTag}"
print {'test' : xyQueryTag}
print "\n-encode({'test' : xyQueryTag})"
encode({'test' : xyQueryTag})

print '\n ***Write query tag to new dataset()***'
newDataSet = system.dataset.toDataSet(list(xyQueryTag.getColumnNames()), 
                                      [[xyQueryTag.getValueAt(row, col) 
                                        for col in xrange(xyQueryTag.getColumnCount())] 
                                        for row in xrange(xyQueryTag.getRowCount())
                                      ]
                                     )
print "\n-print newDataSet"
print newDataSet
print "\n-print {'test' : newDataSet}"
print {'test' : newDataSet}
print "\n-encode({'test' : newDataSet})
encode({'test' : newDataSet})

```

Output

```nohighlight
***PyDataSet***
-print queryResult, type(queryResult)
<PyDataset rows:1 cols:2> <type 'com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet'>

-encode(queryResult)
u'{"columns":[{"name":"x","type":"java.lang.Integer"},{"name":"y","type":"java.lang.Integer"}],"rows":[[1,2]]}'

-print {'test' : queryResult}
{'test': [[1, 2]]}
-encode({'test' : queryResult})
'Cannot encode'

-type(queryResult.underlyingDataset)
<type 'com.inductiveautomation.ignition.common.BasicDataset'>

-print {'test' : queryResult.underlyingDataset}
{'test': Dataset [1R ⅹ 2C]}
{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}

-print type(system.dataset.toDataSet(queryResult))
<type 'com.inductiveautomation.ignition.common.BasicDataset'>

-print {'test': system.dataset.toDataSet(queryResult)}
{'test': Dataset [1R ⅹ 2C]}

-encode({'test': system.dataset.toDataSet(queryResult)})
u'{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}'

***Dataset tag (no query)***
print xyDatasetTag, type(xyDatasetTag)
Dataset [1R ⅹ 2C] <type 'com.inductiveautomation.ignition.common.BasicDataset'>

-encode(xyDatasetTag)
u'{"columns":[{"name":"x","type":"java.lang.Integer"},{"name":"y","type":"java.lang.Integer"}],"rows":[[1,2]]}'

-{'test' : xyDatasetTag}
{'test': Dataset [1R ⅹ 2C]}

-encode({'test' : xyDatasetTag})
u'{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}'

*** Query tag

-print xyQueryTag, type(xyQueryTag)
Dataset [1R ⅹ 2C] <type 'com.inductiveautomation.ignition.common.BasicDataset'>
u'{"columns":[{"name":"x","type":"java.lang.Integer"},{"name":"y","type":"java.lang.Integer"}],"rows":[[1,2]]}'

-print {'test' : xyQueryTag}
{'test': Dataset [1R ⅹ 2C]}

-encode({'test' : xyQueryTag})
'Cannot encode'

 ***Write query tag to new dataset()***

-print newDataSet
Dataset [1R ⅹ 2C]

-print {'test' : newDataSet}
{'test': Dataset [1R ⅹ 2C]}
{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}
>>>

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 23, 2022, 12:09pm UTC](https://forum.inductiveautomation.com/t/jsonencode-dataset-tags-with-query-as-value-source/58654/4 "2022-04-23T12:09:27Z")

</div>

So, for your first workaround:

```python
dsTest1 = system.db.runQuery('SELECT * FROM someTable')
print system.util.jsonEncode({'test' : dsTest1.underlyingDataset}, 4)

```

Second workaround (since addColumn iterates though the dataset already, we may as well do it without the extra data):

```python
tag = system.tag.readBlocking(['[provider]path/to/tag'])[0].value
newDataset = system.dataset.toDataSet(list(tag.getColumnNames()), 
                                      [[tag.getValueAt(row, col) 
                                        for col in xrange(tag.getColumnCount())] 
                                        for row in xrange(tag.getRowCount())
                                      ]
                                     )

```