Subset match in Oracle

Question

I've the following two tables, pod_label & service_selector:

create table pod_label (
    name varchar2(256),
    key varchar2(256),
    value varchar2(256)
);

insert into pod_label (name, key, value)
values ('p1', 'l1', '1');
insert into pod_label (name, key, value)
values ('p1', 'l2', '2');
insert into pod_label (name, key, value)
values ('p1', 'l3', '3');

insert into pod_label (name, key, value)
values ('p2', 'l1', '1');

insert into pod_label (name, key, value)
values ('p3', 'l1', 'N/A');

NAME	KEY	VALUE
p1	l1	1
p1	l2	2
p1	l3	3
p2	l1	1
p3	l1	N/A

create table service_selector (
    name varchar2(256),
    key varchar2(256),
    value varchar2(256)
);

insert into service_selector (name, key, value)
values ('s1', 'l1', '1');
insert into service_selector (name, key, value)
values ('s1', 'l2', '2');

NAME	KEY	VALUE
s1	l1	1
s1	l2	2

I'd like to find name in pod_label such that the corresponding (key, value) pairs are a superset of those of s1 in service_selector. In the above example, p1 is a match, while p2 & p3 are not.

A straight JOIN isn't going to do it. Is there a way w/out resorting to a PL/SQL function?

score 0 · Answer 1 · answered Jun 16 '23 at 22:13

You can create the types:

CREATE TYPE key_value_pair AS OBJECT(
  key   VARCHAR2(256),
  value VARCHAR2(256)
);

CREATE TYPE key_value_list AS TABLE OF key_value_pair;

Then you can aggregate the values into collections and use the SUBMULTISET OF operator to compare collections and ensure that one is a sub-set of the other:

SELECT name, key, value
FROM   (
  SELECT name,
         key,
         value,
         CAST(
           COLLECT(key_value_pair(key, value)) OVER (PARTITION BY name)
           AS key_value_list
         ) AS kvs
  FROM   pod_label
) pl
WHERE  EXISTS(
         SELECT 1
         FROM   service_selector ss
         GROUP BY name
         HAVING CAST(
                  COLLECT(key_value_pair(ss.key, ss.value))
                  AS key_value_list
                ) SUBMULTISET OF pl.kvs
       );

Which, for the sample data, outputs:

NAME	KEY	VALUE
p1	l1	1
p1	l2	2
p1	l3	3

fiddle

astentx · Answer 2 · 2023-06-17T11:24:18.067

You may use the same approach as in this question: SQL query: Simulating an "AND" over several rows instead of sub-querying. The idea is:

match rows by the list of values
count number of matched rows per key
select only those keys that have number of matches equal to a number of criteria (the length of the list)

To extrapolate this approach for multiple lists identified by some column you may use analytic count with partition by that column.

with vals_per_service as (
  /*Number of criteria per service*/
  select
    s.*,
    count(1) over(partition by name) as s_cnt
  from service_selector s
)
, pod_match as (
  select
    s.name as service_name,
    p.*,
    s.s_cnt,
    /*Number of matches per pod and service*/
    count(1) over(partition by s.name, p.name) as match_cnt
  from vals_per_service s
    left join pod_label p
    on p.key = s.key
    and p.value = s.value
)
select *
from pod_match
/*Pods that matched by all criteria*/
where s_cnt = match_cnt
--  or name is null /*If you want selectors without matches*/
order by 1,2

Which for this sample data (extended by other selectors):

select *
from pod_label

NAME	KEY	VALUE
p1	l1	1
p1	l2	2
p1	l3	3
p2	l1	1
p2	l3	3
p3	l1	N/A

select *
from service_selector

NAME	KEY	VALUE
s1	l1	1
s1	l2	2
s2	l1	1
s3	l1	1
s3	l3	none

returns:

SERVICE_NAME	NAME	KEY	VALUE	S_CNT	MATCH_CNT
s1	p1	l1	1	2	2
s1	p1	l2	2	2	2
s2	p1	l1	1	1	1
s2	p2	l1	1	1	1

fiddle

Subset match in Oracle

2 Answers2