Handling empty array from multiselectListbox when searching tags

Queries and Resources
1

Hello. I cannot seem to get this query to work.

I am using multiselectListbox1 as a way to filter results in listView3. Basically a way to filter FAQs with tags. I can get the query below to work perfectly fine as long as there is an item selected in multiselectListbox1 but it returns no results if nothing is selected. I've tried different methods of conditions and using my full array of tags {{ resources_tags.data.Tag }} as an alternative search with no success. If I use an OR expression and i get the empty array error from the empty multiselectListbox. Any ideas?

WHERE
EXISTS (
SELECT 1
FROM UNNEST(Tags) AS tag
WHERE tag IN UNNEST({{ multiselectListbox1.value }})

Example of how I want it to work but getting empty array error:

WHERE
EXISTS (
SELECT 1
FROM UNNEST(Tags) AS tag
WHERE tag IN UNNEST(
CASE
WHEN ARRAY_LENGTH({{ multiselectListbox1.value }}) = 0
THEN {{ resources_tags.data.Tag }}
ELSE {{ multiselectListbox1.value }}
END
)
)

2

@Justin_Fuqua handling an empty array in queries can be tricky, especially with the IN clause and the UNNEST function. When using a multi-select list box, you want to ensure the query behaves correctly when the list is empty or has values.

Here's a solution using conditional logic directly in the query:

WHERE EXISTS (
    SELECT 1
    FROM UNNEST(Tags) AS tag
    WHERE
    -- Check if the multiselectListbox1 has any values
    (CASE
        WHEN ARRAY_LENGTH({{ multiselectListbox1.value }}, 1) = 0 THEN
            -- If it's empty, match any tag in the Tags array
            tag IN (SELECT UNNEST({{ resources_tags.data.Tag }}))
        ELSE
            -- Otherwise, match tags in the multiselectListbox1
            tag IN (SELECT UNNEST({{ multiselectListbox1.value }}))
    END)
)
3

Thank you for your reply, @ZeroCodez .

Unfortunately, I'm still getting the empty array error when listbox is empty

image
image1256×516 58.9 KB

and syntax error when it's not

image
image1270×598 62.9 KB

4

@Justin_Fuqua You can give this code a try.

WHERE EXISTS (
    SELECT 1
    FROM UNNEST(Tags) AS tag
    WHERE
    -- Check if multiselectListbox1 has any values
    (
        (ARRAY_LENGTH({{ multiselectListbox1.value }}, 1) = 0 AND tag IN (SELECT UNNEST({{ resources_tags.data.Tag }})))
        OR
        (ARRAY_LENGTH({{ multiselectListbox1.value }}, 1) > 0 AND tag IN (SELECT UNNEST({{ multiselectListbox1.value }})))
    )
)
5

Same errors. This code below works when I have a selection in the listbox but gets empty array error when nothing is selected. It seems to refuse any query that has an empty array even if it isn't used in the CASE.

WHERE EXISTS (
SELECT 1
FROM UNNEST(Tags) AS tag
WHERE
-- Check if the multiselectListbox1 has any values
CASE
WHEN ARRAY_LENGTH({{ multiselectListbox1.value }}) = 0 THEN
-- If it's empty, match any tag in the Tags array
tag IN UNNEST({{ resources_tags.data.Tag }})
ELSE
-- Otherwise, match tags in the multiselectListbox1
tag IN UNNEST({{ multiselectListbox1.value }})
END
)

6

FYI - I was working around with it a bit and think I found a solution. The issue seemed to be that I was using the results in a listView so it refused any empty arrays whatsover. So, I created a JS that returned results from 2 separate queries (one without the listbox expression and one with) and then used this JS as the data for my listView

// Check if the multiselectListbox1 is empty
let tagsArray = multiselectListbox1.value ;

if (tagsArray.length === 0) {
// Run the query for when the listbox is empty
return resources_page.trigger() ;
} else {
// Run the query for when the listbox has values
return resources_page_listbox.trigger() ;
}

resource_page query:

SELECT
Title,
URL,
Info,
Item,
Tags,
Channel,
Page
FROM
BIGQUERYTABLE
WHERE
LOWER(Title) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(Info) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(Channel) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(Item) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(ID) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))

resource_page_listbox query:

SELECT
Title,
URL,
Info,
Item,
Tags,
Channel,
Page
FROM
BIGQUERYTABLE
WHERE EXISTS (
SELECT 1
FROM UNNEST(Tags) AS tag
WHERE tag IN UNNEST({{ multiselectListbox1.value }})
)
AND (
LOWER(Title) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(Info) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(Channel) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(Item) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
OR LOWER(ID) LIKE LOWER(CONCAT('%', {{ textInput7.value }}, '%'))
)