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"
Could your initial query ONLY return the JSON column and the Primary Key (and any other applicable selection criteria that will work with standard filters) , and then you filter out those rows that don't match your JSONpath expression, and then make a second query to get all of the other data you need? (yes, it's two separate queries, but might perform better than fetching the full length and breadth of the table and then filtering locally)
The table only contains 2 fields at the moment: a primary key and a json object, so that's not necessary.
The problem is I can't seem to apply jsonpath without getting everything first and once I hit around 3,000 rows, I run out of memory.
I suppose that's one way to do it. I was just being told to make use of JSONpath as a possibly more performant solution. But if it won't work, it won't work.
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.
A possible alternative, depending on the database you're working with, is that the DB might support JSON functions and you could use that to conduct the relevant filtering. That would mean you calling a Stored Procedure and either designing it in such a way as to be generic to support any type of query, or having one Stored Proc per concrete query type you know it needs to support. But, as I said, entirely dependent upon your DB's support for JSON functions.
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.