Not In clause with two resources

Currently trying to build a table filled with items that have been created as tickets, but filter out items that have already been created. The resources exist on different databases, so i can't just do this in mysql, its gotta be the json query.

first resource is fairly straight forward.
select * from resourceA

second resource is
select UIC from resourceB

the json query should look like the following in my mind:

select * 
from {{ formatDataAsArray( }} as a
where a.UIC not in  ( {{ formatDataAsArray( ) }} )

This returns all items in resourceA without filtering the correct rows out.
Appreciate any help.


if your resourceB is just an array of non-object types (string, numeric), you can try this

FROM {{resourceA.value}} a
WHERE a.UIC NOT IN (SELECT _ FROM {{ resourceB.value }})

query json

Hope that'll help.

resourceA is a mysql query, so is resourceB, so i'll rename them for clarity.

select * from {{ formatDataAsArray( ) }} where UIC not in ( {{ queryB.dataArray['0'].UIC }} )

getting things to be an array seemed like a good thing to set my compass to so i ended up with this.

Still doesnt seem to be filtering those items out though.

Can you try and replace
( {{ queryB.dataArray['0'].UIC }} )

(SELECT _ FROM {{ queryB.dataArray['0'].UIC }})

let me know if it doesn't work.


Thank you i totally looked over the Select _ FROM

I had to change it to

(SELECT UIC FROM {{formatDataAsArray(}})

but it works, thanks for your help, and patience.

1 Like