Search a DB based on input / Merging Results
Hi All,
I’m a newbie so bear with me. I’m working on a pipeline that connects to a CRM system. This CRM system tracks everything by the customer’s contact ID number. However, our web site uses a...
Forum Discussion
Thanks @bojanvelevski ! I tried the splitter and have moved a little ahead. But one more challenge is that I have to run Selects and Updates one after the other from the JSON. What I mean here is for e.g.
Customer Age : Select a,b from table1;
Stage_table_update_for_age: Update stagetable set age=a (value ‘a’ from above select) where list of conditions.
Customer Sex : Select c,d from table1;
Stage_table_update_for_sex: Update stagetable set sex=c (value ‘c’ from above select) where list of conditions.
There are multiple such selects and updates to be done one after the other irrespective of how many I have. I am not sure what snaps to use for this type of handling.
I am able to get the inputs from the JSON in format above but how can we segregate the selects with the exact update it needs ?
so my mapper has the outputs as listed in the diagram attached. I want to make sure I send these to SQL execute in the same manner as selects and updates for those selects.
Here it shows 3 for Age, Gender and Quantity. But it can be any number of such statements maybe 6 or 8 based on my framework. So should be scalable too.
I tried to attach a pic of the JSON how it looks but I dint find any attachment related button.
How is your order defined in the JSON?
You can do quite a lot with JSON-Path, it’s not limited to statically defined paths like
$foo.bar. The filtering functionality makes it possible to quite a lot of stuff dynamically.That being said, it sounds like you need to be able to recursively remove objects that become empty as their descendants are removed. That kind of a task is out-of-scope for JSON-Path since it works on the full object tree at once. I think you can do what you want in the expression language, though. Since recursion is required, you’ll want to use an expression library to do the job. I’ll attach a library that contains a
cleanupTreefunction that should do what you want. The function takes a value and, if it’s an object, calls itself for each field in the object and then filters out any fields that are empty objects. In other words, it will descend to the leaves of the object hierarchy, remove any fields that have empty objects and, if that empties out an object, that field will be removed.I’ll paste the library code in here since it’s pretty small:
{ // Returns true if the value is not an object or the object has fields. isNotEmpty: val => !(val instanceof Object) || !val.isEmpty(), // Recursively removes empty fields in an object. cleanupTree: root => (root instanceof Object ? root.mapValues(this.cleanupTree).filter(this.isNotEmpty) : (root instanceof Array ? root.map(this.cleanupTree).filter(this.isNotEmpty) : root)) }You’ll want to unzip the attachment on your computer and then import it into your pipeline via the Pipeline Properties dialog. You can then call the function in a Mapper using
lib.objutil.cleanupTree($).objutil.expr.zip (325 Bytes)
THANKS for the help. On the recursive one, with the expression library, I installed the library in the pipeline profile expressions section. I did it as objutil, though I don’t think that matters. I tried the lib.objutil.cleanupTree($) in a mapper connected to the original mapper. It was put in the source section. I didn’t put anything in the target section. When I put it in the source section, it did show a preview of what was there, with … for the values. I even tried to change the name, to see if it gave an error, as I would expect. It did. So it looks like it is installed right. What am I doing wrong?
The reason I want to do this is simply to exclude fields from being updated in the target, selectively. I don’t expect any special processing to be done between it and the target. Between this, and the option del specified, I think this would really help snaplogic’s customers on this type of problem.
Del’s solution would probably work on 80% of the original concern, but the recursive option should work on just about everything.
Steve
You need to put
$in the target path so that the result of thecleanupTree()function is used as the new output value. The ‘Pass Through’ option should also be unchecked. Without$in the target path,lib.objutil.cleanupTree($)is treated as the JSON-Path to delete.
I have a similar issue, but this expression library is not working.
For your specific Address example, I found this expression to work:
$Address.filter((v1,k1)=>!v1.value.filter((v2,k2)=>!v2.isEmpty()).isEmpty())Logically, it also works like this:
$Address.filter((v1,k1)=>v1.value.filter((v2,k2)=>v2.isEmpty()).isEmpty())No recursion though, so @tstack’s solution is definitely a better approach if that’s required.
