JSON return format for Named query in script

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.

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/

Thanks for pointing me in the right direction. I implemented your option b on a postgres db by using the json_agg() function.

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 :frowning:

3 Likes

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.