Trying to query a JSON array with multiple values

App Building
1

I am struggling with writing a query for a json array in my database.

Here is the query I have working:

SELECT
  *
FROM
  table
WHERE
  series IN ('123', '234')
ORDER BY series ASC

I have a Json column in my database that has the following array:

{"series": ["123", "234"]}

I have tried using with no luck:

SELECT
  *
FROM
  table
WHERE
  series IN {{query.data.column[0].series}}
ORDER BY series ASC
2

I think it should look more like this?

SELECT series
  FROM table
    WHERE "123" IN series;

or maybe

SELECT *
  FROM table
    WHERE "123" IN table.series;

I've managed to confuse myself so hopefully one of those is right :laughing:

the docs for IN over here have a bunch of examples that might help out a lot more than me.

3

My goal is to get select all rows from my table, where the series are equal to one in the array.

4

I was able to brute force it for now.

The issue I seem to be having has to do with retool inserting the javascript strings / array in double quotes.

This will work for now but I have to imagine there is a better way.

SELECT
  *
FROM
  table1
WHERE
  (column1 = {{query1.data.queryCell[0].arrayData[0]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[1]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[2]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[3]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[4]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[5]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[6]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[7]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[8]}}
  OR column1 = {{query1.data.queryCell[0].arrayData[9]}})
AND
  column2 = {{query1.data.connector_gender[0]}}
ORDER BY column1 ASC;
5

For an array, you need = ANY([array here]). Try this:

SELECT
  *
FROM
  table
WHERE
  series = ANY( {{query.data.column[0].series}} )
ORDER BY series ASC
1 Like
6

Thank you. I actually found that works earlier today.

Now I just have to figure out how to make it work if there is nothing selected.