Using IN( {{array}} ) from a JSON with SQL query

When I want to use an IN() with an array or a comma delimited list of values, for instance using a multi Select component, I would structure my WHERE clause like this:

status_id in (SELECT value FROM STRING_SPLIT({{bgStatus.value}}, ','))

But this does not work with AlaSQL, STRING_SPLIT is not a recognized function.

I have flailed around trying other things and Googled a lot, I won't bore you with what I tried as they all failed, I cannot fund a way in AlaSQL to get a result set from a delimited string or array.

Is there a way to do this?

What does bgStatus.value look like initially?
I’ve run into a similar issue recently and solved by rewriting the original query. So, I may have a solution.

Thanks for taking a stab. It is a comma delimited string like: "1,2,5,6"

I can of course make it an array easily enough. This is my actually query and it is the first element of the WHERE clause that is the problem:

select * 
from {{formatDataAsArray(qryProjectsSelect.data)}}
where  project_status_id in (select value from STRING_SPLIT({{trProjectFilterStatus.value}}, ','))
AND ({{selFilterProjectType.value || null}} IS NULL OR project_type_id={{selFilterProjectType.value}})
AND ({{selFilterClient.value || null}} IS NULL OR client_id={{selFilterClient.value}})
AND (1 = {{swFilterNeedsAssign.value === true ? 0: 1}} or assignments_filled = false)
AND (1 = {{swFilterNeedsApproval.value === true ? 0: 1}} or swFilterNeedsApproval = false)
AND ({{dtFilterStartBefore.value || ""}} ='' OR DATE(date_time_end)<=DATE({{dtFilterStartBefore.value}}))
AND ({{dtFilterStartAfter.value || ""}} ='' OR DATE(date_time_start)>=DATE({{dtFilterStartAfter.value}}))

I have a segmented button that is acting as a toggle between Incomplete and Complete is used as a quick filter on project_status_id. The user can alternatively select a specific status using selFilterStatus. The transformer has the logic to determine which statuses should be selected:

// trProjectFilterStatus
let selStatus = {{selFilterStatus.value}}
let togStatus = {{segFilterStatus.value}} 

if (selStatus) {
  return selStatus.toString()
}
if (togStatus) {
  return "7"  
}
return "1,2,5,6"

I have an idea for a brute force method, but it I'd rather not go there yet. I can also just hit the server instead, but I prefer the JSON SQL method for performance reasons.

I never found a resolution using AlaSQL so I reverted back to direct query of data which I think you will have to do the same; unfortunately

Hey @bradlymathews!

Can you try status_id in @({{bgStatus.value}}) and let us know if that works? AlaSQL has some specific syntax for approximating JavaScript in various cases that may be worth checking out.

1 Like

Yabba Dabba do! That does it. Thanks!

@Kabirdas
Do you have an example where @> can be used? I have one array and want to compare it against another array using query json with SQL

:thinking: I'm not entirely sure but will try and find a good example, what comparison would you like to do?

does the array in the table field contain any of the selected values from a multiselect dropdown (which is an array)

Haven't found a good answer here... In the spirit of convoluted AlaSQL queries, it's at least possible to use SEARCH:

My guess is that there's a much cleaner way to do this though :sweat_smile: curious to see if anyone else has the answer.