I have two tables:

A

And B:

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?

CodePudding user response：

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

CodePudding user response：

It could be done like this:

SELECT 
sum(clicks) as clicks, row_id, campaign, content, keyword
FROM B,A
WHERE row_id = split_part(a.id,'-',1)::integer  
AND campaign = split_part(split_part(a.id,'campaign=',2),'&',1) 
or content = split_part(split_part(a.id,'content=',2),'&',1) 
or keyword = split_part(split_part(a.id,'keyword=',2),'&',1)
or "source" = split_part(split_part(a.id,'source=',2),'&',1)
GROUP BY row_id, campaign, content, keyword, "source";

attending comments a better solution could be:

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";