Match two tables with query with different parameters

user_18101106 0
Viewed 69

I have two tables:

A

id
1234-campaign=main&content=video&keyword=man
4321-campaign=cast&source=youtube

And B:

row_id campaign content keyword source clicks
1234 main video man undefined 124
4321 cast undefined undefined youtube 10
5312 cast undefined undefined undefined 9999

I want to build dynamic query to find stat in table B for rows in table A. So for first row it should be like:

SELECT 
sum(clicks) as clicks, row_id, campaign, content, keyword
FROM B
WHERE row_id = 1234 AND campaign = 'main' and content = 'video' and keyword = 'man'
GROUP BY row_id, campaign, content, keyword

And for second row:

SELECT 
sum(clicks) as clicks, row_id, campaign, source
FROM B
WHERE row_id = 4321 AND campaign = 'cast' and source = 'youtube'
GROUP BY row_id, campaign, source

So.. I want to build a result view for all rows in table A. I need to find and return result from table B for ids in table A, but id is complex, and first I need to parse id somehow. Any ideas?

postgresql sql
2 Answers

Convert things to jsonb and use the @> (contains) operator:

select *
  from b
       join a
         on to_jsonb(b)||jsonb_build_object('text_row_id', b.row_id::text)
              @> jsonb_object(
                             regexp_split_to_array(
                               'text_row_id-'||a.id, '[-&=]'
                             )
                           );

db<>fiddle here

user_138083190

It could be done like this:

select sum(clicks) as clicks, row_id, campaign, content, keyword
from B inner join A
on (b.row_id = split_part(a.id,'-',1)::integer  
and b.campaign = split_part(split_part(a.id,'campaign=',2),'&',1))
group by row_id, campaign, content, keyword, "source";

attending to comments another solution would be "deactivating optional parameters" when not present, only valid for type text fields, however could be redundant with grouping sentence:

select sum(clicks) as clicks, row_id, campaign, content, keyword
from B inner join A on (b.row_id = split_part(a.id,'-',1)::integer  
and b.campaign = split_part(split_part(a.id,'campaign=',2),'&',1)
and b."content" ~~ coalesce (nullif(split_part(split_part(a.id,'content=',2),'&',1),''),'%' )
and b.keyword ~~ coalesce (nullif(split_part(split_part(a.id,'keyword=',2),'&',1),''),'%' )
and b."source" ~~ coalesce (nullif(split_part(split_part(a.id,'source=',2),'&',1),''),'%' ))
group by row_id, campaign, content, keyword, "source";
user_150623610
Related