Merged Result API

Hi @kpmartin87,

Thanks for writing in. Would you mind elaborating on what you’re trying to do with the API and merge results (i.e. Are you trying to run a dashboard with an merge result tile via the API? Add a merge result tile to a dashboard? Etc.)?

Best,

Elliot

Gotcha, thanks for the context Kyle!

While it’s currently not possible to run merged queries (and get merged query results) via the API, the ability to do so will come with the official release of Looker API 3.1. There’s no firm timeline on when API 3.1 will be released, but keep your eyes on our release notes for more information!

Have a good one,

Elliot

Hi, @Ellio, is there a way to run merged queries via the API in the latest version?

Is this possible yet? Had a quick look around and couldn’t see anything simple yet?

OK, so technically it is possible. Feasible maybe not so. If you are ok with each of the merged queries in the tile being a separate file and you plan to just merge the files elsewhere / manually, this is super easy. If you want to download each query into one file as if it were a normal dashboard tile AND have unlimited results this gets a bit more tricky. 

In either case, the first step ends up being the same; get the merge query ids from `dashboard_element`, get query details for each of the queries from the `merge_query` endpoint, and change the `limit` to `-1` (unlimited). This can end up looking something like this:

 data = sdk.search_dashboard_elements(dashboard_id=dash_id, title=tile_title )
 mergeid = data[0].merge_result_id

 # hit merge_query to get source query ids
 mergedata = sdk.merge_query(merge_query_id=mergeid)
 source_qs = mergedata.source_queries
 source_qids = []
 num_merged = len(source_qs)

 for x in range(num_merged):
   source_qids.append(source_qs[x].query_id)
   query_body = sdk.query(query_id=source_qids[x])
   elements_needed = ['model','view','fields','filters','sorts','limit','query_timezone']
   query_body['limit'] = '-1'
   listlen = len(elements_needed)
   final_body = {}

If you’re ok with having each query saved as a different tile, you can then just iterate through running each of the queries and saving as a resultset then writing to a file. Could look something like this:

< some for loop goes here >
 ...
  resultset = sdk.run_inline_query('csv', final_body)

  with open(tile_title + 'q '+str(x)+'.csv', 'w', newline='') as file:
         file.write(resultset)

If you want to save everything unlimited into the same file, you end up needing to do a bit of heavy lifting outside the API. The easiest way I’ve found is just to use a pandas dataframe for each query and then do a merge in a similar way that Looker merges merged results under the hood (like a left join). That can be computationally expensive to the machine you’re downloading on though, depending on how big the query result sets are. 

Full scripts here:

• Download as separate files: https://github.com/NestleJsCrunch/LookerScripts/blob/main/DownloadUnlimitedMergedv1.ipynb
• Download as one file: https://github.com/NestleJsCrunch/LookerScripts/blob/main/DownloadUnlimitedMergedv2.ipynb

Hope this helps!