I have a setup where a particular column of a table contains a JSON object that I need to search through. I basically want to go through each row in that table and use JSONpath to filter out results, but I am unsure how to apply that to an appian query of the data. I figure I use JSONpath as an expression in a filter, something like the image below, but I don't know what I would enter for the required 'value' parameter. Is there something like fv!item which is used in foreach loops, but used in query filters that I can use here? Otherwise, if I am going about this all wrong, can someone explain how to use jsonpath within an appian query so I don't have to do something like query everything and filter it afterwards with JSONpath?
Discussion posts and replies are publicly visible
I don't believe query filters can make use of jsonPath whatsoever. Wouldn't it generally be sufficient to use an "includes" filter for the json attribute, i.e. where the json column includes `"gender":"female"` ?
"gender":"female"
I ran into an issue trying that. I may need to compare values as part of the filtering process. For example, I might want to see if their salary field is above a particular threshold. Includes wouldn't be sufficient in such a case.
For that sort of thing to work with a!queryEntity, you'd need to store that data in traditional DB columns, unless you're prepared to do something closer to what Stewart suggested and pre-query a bunch of rows and manually filter through them on the Appian side. One note here would be, depending on your use case you could potentially limit your pre-query to a more reasonable set of rows by filtering first on other criteria.
Believe me, if I could store the data in traditional db columns, I would. It would make everything a lot easier. Unfortunately, the format of the data is out of my hands.
As for filtering down to a more reasonable set of rows, there are no criteria that I can filter outside of the json stuff as the only two fields on the table are the ID and the json object.
When you pass a query into them, read only grids seem to handle whatever logic is needed to bring in the data in smaller batches to see on individual pages. Is there some way to apply that sort of logic elsewhere? Loop through the entire data, but in smaller, more manageable batches?
Marco said:As for filtering down to a more reasonable set of rows, there are no criteria that I can filter outside of the json stuff
I was thinking along the lines of pre-filtering based on the JSON information (if there's anything you can directly filter on, like the "gender" example we did before). If not then you'll have to skip this part.
Marco said:read only grids seem to handle whatever logic is needed to bring in the data in smaller batches to see on individual pages
This is all handled on the back end by a!queryEntity with paging info. Read-only grids are merely a visual manifestation of this. Problem being, if there's no "text includes" filtering you can do on your JSON column, and no way to otherwise break this data out to other proper columns, there's not much you can do that will allow you to do very much inside of a!queryEntity. At this point I'd be considering whether it's possible to build a view that breaks out some of your json data (at least for columns that will always exist) into discrete columns. MariaDB has various JSON functionality available but as far as this specific thing, I haven't necessairly tried this.
Are you genuinely constrained in how the data is populated into the DB? For example: the current process that loads the data (as a PK, JKSON blob pair) - could that be expanded to extract the data from the incoming JSON and load into a normalized model that you could then implement as you were initially hoping to do so using standard query entity calls? In short: is there an opportunity to move the problem upstream to the database load process, rather than where you're currently trying to solve it?
I hadn't thought of that. Hmm... In this particular case the json objects are uniform so the table could be set up to take everything in, I suppose. I'd probably need to get permission to do things that way though. I'll take it up with them to see. Thanks for the idea.