user3770935
user3770935

Reputation: 318

Return which element matched each row in SQL result

I am programmatically writing SQL queries from user input. I want to show the user for each row returned, which part of their supplied query information matched against that row.

For instance user supplied information looks like:

red:[11,202]
blue:[36]
green:[202]
yellow:[11,36]

This information would build the following query:

SELECT name, list FROM test WHERE list SIMILAR TO '%(11|202|36)%'

But I would like to add a result field similar to the "Matched Element shown below:

name list Matched Element
foobar 15,11,19,20 red, yellow
hello 17,30,36,20 blue, yellow
bar 101,202,330,460 red, green
test 15,36,23 blue

I cannot add attempted examples as I do not know the proper language by which to seach for information. My current solution so far is to process the data row by row after the query has returned the result to my script to add the new column, but I would like to know if its possible to achieve this using postgres.

Here is a db-fiddle: https://www.db-fiddle.com/f/nMMB3wVFRTGeZgobmA4F6k/1

I am running postgresql 12.3 however I am able to change version if needed.

Upvotes: 1

Views: 65

Answers (2)

Jeremy
Jeremy

Reputation: 6723

I agree with Hambone that you should take advantage of arrays. If you don't want to create an additional table, you could also do it all in one query:

select name, list,
  array_to_string(ARRAY[
      CASE WHEN list LIKE ANY(ARRAY['%11%', '%202%']) then 'red' else null end,
      CASE WHEN list LIKE ANY(ARRAY['%36%']) then 'blue' else null end,
      CASE WHEN list LIKE ANY(ARRAY['%202']) then 'green' else null end,
      CASE WHEN list LIKE ANY(ARRAY['%11%', '%36%']) then 'yellow' else null end
    ], ', ')
from test 
where list LIKE ANY(ARRAY['%11%', '%202%', '%36%', '%202%', '%11%', '%36%']);

Note that I'm using LIKE ANY instead of SIMILAR TO. You could continue to use SIMILAR TO, but it seems easier to me to use LIKE ANY.

Here's a fiddle.

Upvotes: 2

Hambone
Hambone

Reputation: 16387

It sounds like you really need to take advantage of PostgreSQL's most excellent array support. I feel like I say that a lot.

If you change your structure to arrays:

create table test2 (
  name varchar(255),
  list integer[]
)

Then, notionally you can even put your input into another table:

create table matches (
  color text,
  list integer[]
);

insert into matches values 
('red', '{11,202}'),
('blue', '{36}'), 
('green', '{202}'), 
('yellow', '{11,36}');

The following query should yield the results you seek:

select
  t.name, t.list, array_agg (m.color)
from
  test2 t
  join matches m on
    m.list && t.list
group by
  t.name, t.list

Upvotes: 2

Related Questions