Copy Query JSON

Ryan Carr

In Web View > Query > Options, there is an option to “Copy Query JSON”. What format is the code in this feature in? I have a flow that creates an ad hoc query, and I assumed this button might provide the JSON of a query I created in that format, but it doesn't seem to be the case. Can anyone provide some clarity as to how one is supposed to utilize this code?

Alex Wong

@Ryan Carr
You are correct, the code is in JSON format, specifically for use in Adhoc query. However, you cannot directly use it “only”.

https://developer.sky.blackbaud.com/api#api=query&operation=StartQueryExecutionJob

The “body” of the request needs to look like this:

{

“query”: {},

}

The JSON copy from webview query goes INSIDE the red {}.

and you may (and should) specify other parameters (for example):

{

“query”: {},

“formatting_mode”: “Export”,

“output_format”: “JSON”

}

David Zeidman

@Alex Wong @Ryan Carr Funny that. I just added a idea to the idea bank about this very concept:

However, I don't see the copy query JSON that you are describing. Where is that located? I can see the cog on the query screen but that only has one option to see the short or long code in queries. Is there somewhere else where this would appear?

Ryan Carr

@David Zeidman On the Options tab of a Query, at the bottom, is the “Copy query JSON” button.

Ben Lambert

@Ryan Carr I'd be interested in knowing more about your use case for a flow that starts an ad hoc query job (which I know is supported by the connector). Could you elaborate on that? Are you taking the query JSON from web view as-is, or are you modifying the JSON with different output fields or criteria?

Alex Wong

@Ben Lambert
I use ad hoc query almost exclusively. Main reason is to not rely on a saved query in RE (in case someone ignore all the warning in query name and description that say don't modify don't delete use in automation, yes, while the query can be saved using option that does not allow change, any RE admin have right to all queries, and can accidently delete).

I wrote a post a while ago before the “Copy JSON” button was available: https://community.blackbaud.com/forums/viewtopic/491/66976

while you don't need to do the same anymore, it mentioned all the properties that can be deleted (i.e. “type”, “category_id”, “others_can_execute”, etc) as it is meaningless to an ad hoc query.

There is also one type of automation that you can only use Adhoc query, which is one where you want dynamic-ness in the query criteri/filter/sort. For example, in the event page, being able to run query against this specific event to display this event's information. You will need to get the context_id for the event and dynamically generate the JSON that filter on event system record id = the event page you are on.