system.db.runNamedQuery return in JSON rather than dataset

[Robert_Hrynciw]

Hello all,

I just added dropdowns to different columns and made my table very interactive with the user. I have filters and a clear button that will filter/reset, however they return a dataset and it gets rid of all of the things I've added to the table. Does anyone know how to return JSON rather than a dataset?

I tried following option A; however, it did not work for me. I don't want to try option B as the database is being used elsewhere.

JSON return format for Named query in script

I think two ways you could accomplish the reduction in overhead are by either: A. You can actually try this, it may give you exactly what you are looking for: system.util.jsonEncode(system.dataset.toPyDataSet(system.db.runNamedQuery(project, path, params))) Converting it into a pyDataset, and then into Json, however the compute overhead would still be done on the client, it just may be faster. B. Moving the formatting onto your database and adjusting your query to format the data into a JSON…

Thanks in advance.

[justinedwards.jle]

I'm not fully understanding the root problem here. We are making temporary changes at a client level that don't write back to the database, and these changes are getting overwritten or lost by actions that pull data from the database?

Could you shed some more light on the root problem as well as the desired flow of information, so we can determine the best solution to your problem?

Normally, if iteration or some type of script manipulation is needed for a dataset returned by a named query, a conversion to a pydataset is performed rather than a conversion to JSON.

[Robert_Hrynciw]

Alright, so I have a JSON table where a few of the columns are dropdowns. I have filters and dropdowns outside of the table that will modify what the user sees (running different queries). The problem I'm having is that the queries inside of the filters are returning a dataset, which shuts off my JSON table with the columns inside of the table as it's a dataset. I need to find a way to return the queries as JSON rather than dataset.

VS

after use of my dropdown filter. The dropdown filter is a simple system.db.runNamedQuery script.

[Robert_Hrynciw]

I found another forum post where someone took the returned dataset from a system.db.runNamedQuery and modified it into a dict. Here is my finished script that returns JSON to the table after a filter button has been pressed:

	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery(query, params))

	NewDict = {'%s' % i: 
		{colName: value
        for colName, value in zip(returnedData.columnNames, list(row))}
   		for i, row in enumerate(returnedData)}

	self.getSibling("Table").props.data = NewDict.values()