#postgresql query help

1 messages · Page 1 of 1 (latest)

spare lance
#

postgresql query that selects records from tickets table where status equals selections from a multiselect widget, and the string value in the techs column contains any of the values selected in a second multiselect widget.

spark saddleBOT
#

Hello, I'm CodeSmith, your Appsmith support bot, ready to assist you with your
query! To construct a PostgreSQL query that selects records from a tickets
table based on two criteria: - The status equals any of the selections from a
multi-select widget (let's say the widget is named MultiSelectWidget1). - The
techs column contains any of the values selected in a second multi-select
widget (let's name it MultiSelectWidget2). You would write a query like this:

({{MultiSelectWidget1.selectedOptionValues}}) AND techs LIKE ANY
(ARRAY[{{MultiSelectWidget2.selectedOptionValues.map(item => '%' + item +
'%')}}]); ``` The `{{MultiSelectWidget1.selectedOptionValues}}` and
`{{MultiSelectWidget2.selectedOptionValues}}` are bindings that bring in the
selected values from your respective multi-select widgets. The `.map()` function
applied to `MultiSelectWidget2`'s values wraps each selected item with wildcard
characters for the `LIKE` condition to work properly. Make sure that your widget
names match the actual names you've used in the Appsmith editor and that the
column and table names match your database schema. If you have any further
questions or need additional assistance, feel free to ask! If you don't find my
response helpful, you can request to speak to a human agent
spare lance
#

I am getting the error: ERROR: operator does not exist: jsonb ~~ character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

spark saddleBOT
#

I apologize for the confusion earlier. The error you are receiving indicates
that there is a mismatch in data types between the column and the values you're
attempting to match. If you're storing data in a column of type jsonb and are
trying to use the LIKE operator, which is not directly compatible with
jsonb, you need to cast your jsonb data to text before using the LIKE
operator. Here's how you can adjust your query: sql SELECT * FROM tickets WHERE status = ANY ({{ MultiSelectWidget1.selectedOptionValues }}) AND techs::text LIKE ANY (ARRAY[{{ MultiSelectWidget2.selectedOptionValues.map(item => '%' + item + '%') }}]); In this query, techs::text casts the jsonb
column techs to text, which should work with the LIKE operator. However,
keep in mind that using LIKE with jsonb cast to text might not behave
exactly as you might expect when working with JSON data structures because the
LIKE operator will perform a text pattern search on the text representation of
the jsonb data. For more complex JSONB queries, you might need to use JSONB
functions and operators to manipulate and search the data. Again, make sure your
Appsmith widget names and your database identifiers match. Try this revised
query, and if you face any more issues, let me know, and I'll be glad to help
further! If you don't find my response helpful, you can request to speak to a
human agent