SQL to only return table values found in array values-CodePudding

SQL noob trying to return db results if a table value is found in an array. I'm using Node JS and Postgres.

So far, I can figure out how to return the result for a single item:

SELECT * FROM table WHERE position(value in 'someval1')>0
// returns [{ id: 1, value: 'val1' }]

I don't know how to replace the single item above ('someval1') with an array.

EXAMPLE

Given the following table:

id	value
1	val1
2	val2
3	val3

and the array: ['foo', 'someval1', 'anotherval2', 'bar']

how might I check that table "value" exists within each array item to return:

[
  { id: 1, value: 'val1' },
  { id: 2, value: 'val2' }
]

CodePudding user response：

This is backwards. Normally you would do something like...

select * from theTable where searchField like '%text1%' or searchField like '%text2%'

But you seem to be trying to pull rows from a table when the row's data is contained by any of several values in the criteria array. What I think you might be looking for is temporary tables (though this exact syntax might be deprecated soon and I don't see the replacement at the moment). You would create the temporary table with just the one field with your text values from the array in it. Then you can use table joining to get the behavior you're trying for. Something like...

select dt.*, tt.SearchString  
from DataTable dt 
right join TempTable tt 
on tt.SearchString like '%' dt.value '%'

And I didn't run that through a tester of any kind, but with the right join, it should only include rows that have a match in the temp table. Assuming my code has typos in it, look into the temp tables (or alternative) and then joins.

Edit: Figured I'd offer a little more on the temporary tables since you mentioned being new to SQL. I think you'd want code like...

create temporary table TempTable (searchString varchar(100));
insert into TempTable values 
    ('foo'), 
    ('someval1'), 
    ('anotherval2'), 
    ('bar');

And of course, you're running that before you do the join with the real table.

CodePudding user response：

Okay, I thought of a slight variation of the approach you can use if temp tables aren't an option (which is why I'm making this a separate answer in case it's the one that ends up working for you.

Create a normal table you use specifically for the purpose of containing the array values. If multiple users might be running this process at the same time, you'll need an ID (session ID, user ID, something reliably unique) to make sure you're only using the values relevant to that person. So, you'd want a table like...

create table SearchTerms (ID int not null AUTO_INCREMENT, sessionID varchar(25), searchTerm varchar(50));

Then, when you run your queries, you start with inserts to load the terms...

insert into SearchTerms values
    ('0129205017_smith', 'foo'), 
    ('0129205017_smith', 'someval1'), 
    ('0129205017_smith', 'anotherval2'), 
    ('0129205017_smith', 'bar');

Next, you run your actual query that looks like...

select dt.*, st.searchTerm 
from SearchTerms st 
left join DataTable dt 
  on st.searchTerm like '%' dt.value '%' 
where st.sessionID = '0129205017_smith';

And then you might want to do clean up so the search terms table doesn't get clogged with stuff...

delete from SearchTerms where sessionID = '0129205017_smith';

CodePudding user response：

You might consider using regex. It is slightly less performant than LIKE, but unless your dataset really needs a lot of optimization you will probably never notice. I have also included an alternative that uses a join to a list of values and LIKE to produce the same results. See this fiddle. I have set the version to postgres 14, since you didn't specify:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7062f6828a161c934c3e8ef96110dd05

CREATE and INSERT:

create table SearchTerms (ID serial primary key, sessionID varchar(25), searchTerm varchar(50));
insert into SearchTerms (sessionID, searchTerm) values
('0129205017_smith', 'foo'), 
('0129205017_smith', 'someval1'), 
('0129205017_smith', 'anotherval2'), 
('0129205017_smith', 'bar');

Solution using regex (~):

select * from searchTerms 
where searchTerm ~ 'val1|val2';

Solution using LIKE and a join to a list of values:

select * from searchTerms s
join (SELECT 'val1' as term UNION ALL SELECT 'val2' as term) v
on s.searchTerm like concat('%', v.term, '%');

I personally prefer regex over like for its flexibility and terseness, and I very rarely find a situation where using LIKE offers sufficient performance improvements to use it in favor of regex.