SQL Replace not working in Query JSON with SQL

I have a Query JSON with SQL that should compare two different Resources:

select * from {{ Routen.data }} where {{ Routen.data.product_route }} not in @({{ Angebotstypen.data.szVariantenMatchCode }})

But the Routen.data.product_route looks like 7561.5226.101 and the Angebotstypen.data.szVariantenMatchCode like 7550-5226-101. How can I compare just the numbers without the delimiters? I tried to use SQL REPLACE for both of them but it did not work. Thanks for your help.


select * from {{ Routen.data }} where {{ Routen.data.product_route }} != ANY({{ Angebotstypen.data.szVariantenMatchCode }})

In addition to trying the != ANY clause, I think you need to split and join both sets to get the comparison you are looking for:



Hey @thim-jbm! Happy to help here. @ScottR @pyrrho Thanks for the assist!

Not 100% sure what your data looks like, but tried to replicate it. A few syntax changes and some split/join action and got it to work!

For these 2 data sets

Was able to grab the 3 items from dataSet1 which were not in dataSet2 with this query

  • You should just be able to reference the object property in the where clause (ie: product_route instead of Routen.data.product_route).

  • The use of NOT IN is fine here.

  • I just chose to modify 1 of the datasets to match the other (remove the '-' and add a '.') but you could remove the delimiters in both.

Let me know if this works for you!

1 Like