# JSON return format for Named query in script **URL:** \ **Category:** Ignition\ **Tags:** ignition79, ignition80\ **Created:** [April 14, 2020, 4:29pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923 "2020-04-14T16:29:21Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Phil\_B](https://avatars.discourse-cdn.com/v4/letter/p/c89c15/32.png) [@Phil\_B](https://forum.inductiveautomation.com/u/Phil_B)\ **Post date:** [April 14, 2020, 4:29pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/1 "2020-04-14T16:29:21Z")
Is there an easy way to get a named query called from within a script to have a return format of JSON similar to the option given if calling a named query from a property binding? Currently i loop through the returned dataset and create the JSON format myself, however for queries with a large number of returned rows this really slows down execution time. ---
**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\ **Post date:** [April 14, 2020, 4:59pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/2 "2020-04-14T16:59:21Z")
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 structure at runtime, so that when you are querying data it returns a JSON string that you then use a system.util.jsonDecode() and parse it into the object that you need to use? Something like this should help you accomplish that [https://www.sqlshack.com/convert-sql-server-results-json/](https://www.sqlshack.com/convert-sql-server-results-json/) ---
**Author:** ![Phil\_B](https://avatars.discourse-cdn.com/v4/letter/p/c89c15/32.png) [@Phil\_B](https://forum.inductiveautomation.com/u/Phil_B)\ **Post date:** [April 14, 2020, 5:29pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/3 "2020-04-14T17:29:23Z")
Thanks for pointing me in the right direction. I implemented your option b on a postgres db by using the json\_agg() function. ```auto Select json_agg(t) FROM ("OLD Query") t ``` This returned a one cell dataset with the json object perfectly formatted within. Now to update all the places where we did this manually ☹ ---
**Author:** ![Subbarao.B](https://avatars.discourse-cdn.com/v4/letter/s/22d042/32.png) [@Subbarao.B](https://forum.inductiveautomation.com/u/Subbarao.B)\ **Post date:** [September 17, 2021, 11:26am UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/5 "2021-09-17T11:26:17Z")
loss\_id = your named query dataset pyData= system.dataset.toPyDataSet(loss\_id) for row in range(pyData.getRowCount()): ``` self.getSibling("loss_code_lbl").custom.loss_id= pyData.getValueAt(row,'id') ``` you can try in this way. also. ---
**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\ **Post date:** [March 1, 2022, 8:42pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/6 "2022-03-01T20:42:25Z")
I just want to throw this ideas post I the thread, hopefully people see it, agree this would be really convenient native functionality, and IA can add it in a release! The more Perspective focused a project becomes, often the more you end up working with arrays of objects instead of datasets. It would be an awesome feature if you could grab the data this way without having to execute another script to convert it every time you want to manipulate it. > **[Query return json in scripting | Voters | Inductive Automation](https://ideas.inductiveautomation.com/ignition-features-and-ideas/p/query-return-json-in-scripting)** ---
**Author:** ![dsmith1](https://avatars.discourse-cdn.com/v4/letter/d/34f0e0/32.png) [@dsmith1](https://forum.inductiveautomation.com/u/dsmith1)\ **Post date:** [November 28, 2022, 2:40pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/7 "2022-11-28T14:40:42Z")
Just wanted to chime in, since I was searching for this answer myself. This proposed solution doesn't seem equivalent to what I want, as there are differences between a named query binding with "json" return format and JSON encoding a PyDataSet. For example, using a namedQuery binding with json format gives the following: ```json { "Id": 1, "IsCritical": false, "Order": 1, "ValueTypeId": 1, "ValueType": "float", "ValidationOperatorId": 3, "ValidationOperator": "Greater Than", "ComparisonValueFloat": 7, "ComparisonValueInt": null, "ComparisonValueBool": null, "ComparisonValueString": null, "ComparisonValueDatetime": null, "ComparisonValueDropdown": null } ``` But using the proposed solution (`system.util.jsonEncode` after using `system.dataset.toPyDataset`) gives the following: ```json {"columns":[{"name":"Id","type":"java.lang.Long"},{"name":"IsCritical","type":"java.lang.Boolean"},{"name":"Order","type":"java.lang.Integer"},{"name":"ValueTypeId","type":"java.lang.Long"},{"name":"ValueType","type":"java.lang.String"},{"name":"ValidationOperatorId","type":"java.lang.Long"},{"name":"ValidationOperator","type":"java.lang.String"},{"name":"ComparisonValueFloat","type":"java.lang.Double"},{"name":"ComparisonValueInt","type":"java.lang.Integer"},{"name":"ComparisonValueBool","type":"java.lang.Boolean"},{"name":"ComparisonValueString","type":"java.lang.String"},{"name":"ComparisonValueDatetime","type":"java.util.Date"},{"name":"ComparisonValueDropdown","type":"java.lang.Long"}],"rows":[[1,false,1,1,"float",3,"Greater Than",7.0,null,null,null,null,null]]} ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [November 28, 2022, 2:58pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/8 "2022-11-28T14:58:31Z")
I have those functions in my project library, maybe this can be enough for most people - it is for me: ```python def to_dicts(ds): """ Make a list of dicts from a dataset. Each row in converted to a dict, with keys matching the dataset's columns names. """ columns = system.dataset.getColumnHeaders(ds) return [dict(zip(columns, row)) for row in system.dataset.toPyDataSet(ds)] def make_key(row, columns, keys): if isinstance(keys, list): return '_'.join(str(row[columns.index(k)]) for k in keys) else: return str(row[columns.index(keys)]) def to_dict(ds, keys): """ Make a dict from a dataset. Each row is converted to a dict key/value pair - the key built from the values (cast to strings) in the columns specified by keys, - the value is a dict with all the values in that row, and keys matching the columns names. """ columns = system.dataset.getColumnHeaders(ds) return { make_key(row, columns, keys): dict(zip(columns, row)) for row in system.dataset.toPyDataSet(ds) } ``` `to_dicts` is straightfoward, it creates a list of dictionaries, one for each row in the dataset. `to_dict` does pretty much the same, except instead of a list of dicts, it's a dict of dicts, the key being the values in the columns passed as the 'keys' argument. > **example** > > ```python > headers = ["foo", "bar", "baz"] > data = [ > ["foo1", "bar1", "baz1"], > ["foo2", "bar2", "baz2"], > ["foo3", "bar3", "baz3"], > ["foo4", "bar4", "baz4"], > ] > ds = system.dataset.toDataSet(headers, data) > > >>> print json.dumps(utils.dataset.to_dicts(ds), indent=2) > [ > { > "foo": "foo1", > "bar": "bar1", > "baz": "baz1" > }, > { > "foo": "foo2", > "bar": "bar2", > "baz": "baz2" > }, > { > "foo": "foo3", > "bar": "bar3", > "baz": "baz3" > }, > { > "foo": "foo4", > "bar": "bar4", > "baz": "baz4" > } > ] > > >>> print json.dumps(utils.dataset.to_dict(ds, 'foo'), indent=2) > { > "foo1": { > "foo": "foo1", > "bar": "bar1", > "baz": "baz1" > }, > "foo2": { > "foo": "foo2", > "bar": "bar2", > "baz": "baz2" > }, > "foo3": { > "foo": "foo3", > "bar": "bar3", > "baz": "baz3" > }, > "foo4": { > "foo": "foo4", > "bar": "bar4", > "baz": "baz4" > } > } > > ``` ---
**Author:** ![Phil\_B](https://avatars.discourse-cdn.com/v4/letter/p/c89c15/32.png) [@Phil\_B](https://forum.inductiveautomation.com/u/Phil_B)\ **Post date:** [November 28, 2022, 2:59pm UTC](https://forum.inductiveautomation.com/t/json-return-format-for-named-query-in-script/33923/9 "2022-11-28T14:59:10Z")
I would suggest looking for the JSON function for your specific database like i showed in my previous comment on the thread for a Postgres install. that was the easiest way i found because i also was having issues with the pyDataSet and Encoding method.