using JSONpath in a query

Question

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?

Mike Schmitt · Answer

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" ` ?

Mike Schmitt · Answer

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.

Mike Schmitt · Answer

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.

Chris · Answer

I would lean toward the DB side as well as you may have functions available that could be used to create a view to make this much easier on the Appian side. What DB/version are you on? For example in MSSQL you may be able to take advantage of OPENJSON() , or JSON_VALUE / JSON_TABLE in Oracle, etc.

using JSONpath in a query

Top Replies