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
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.
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.
Thanks, that's another option we've been looking into. Haven't gotten it to function yet, but it seems promising at least.
We're using MariaDB. We've been looking into using json_table, but haven't figured out how to get it to work yet. Thanks for the tip.
I am working on the similar use case, any luck with JSON_TABLE function. I see Appian does not have much to explore but we have some options from DB and Java API which can be useful for this particular use case. Appreciate if you have share any tips / workaround you did for this use case