query for many to many record matching

Question

I have table tag_store like below

I want to filter the ids which has all tag provided in a like

SELECT st.id  from public."tag_store" st
                inner join 
                    (SELECT  x.tg_type,x.tg_value FROM json_to_recordset 
                     ('[{ "tg_type":1, "tg_value ":"cd"},{ "tg_type":2,"tg_value ":"tg"},{ "tg_type":3,"tg_value ":"po" }]  '::json) 
                     AS x (tg_type int, tg_value TEXT)) ftg
                    on  st.tg_type= ftg.tg_type
                        and  st.tg_value = ftg.tg_value order by st.id;

My desired output is it should have output onlye id 1 as it has all three tg_value and tg_id matched..

Please help, what should I change, or is there any better alternate

Thanks

So you want to pass a list of tag_values to the query and only get the IDs that have **all** of them? — , Nov 26 '20 at 10:20
tag values along with tag_id... it should match tag value and tag id and returns only those ids which have all combination of tag_id and tag_value — Md. Parvez Alam, Nov 26 '20 at 10:21
The `tag_id` column confuses me. What has `tag_id = 1` the value `cd` for one row, but `xx` for another? — , Nov 26 '20 at 10:22
Yes... yes it has mater table where tag_id and its type stored — Md. Parvez Alam, Nov 26 '20 at 10:23

score 2 · Accepted Answer · 2020-11-26T10:48:41.100

2

I would aggregate the values into a JSON array and use the @> operator to filter those that have all of them:

with tags as (
  select id, jsonb_agg(jsonb_build_object('tg_id', tag_id, 'tg_value', tag_value)) all_tags
  from tag_store
  group by id
)  
select *
from tags
where all_tags @> '[{"tg_id":1, "tg_value": "cd"},
                    {"tg_id":2, "tg_value": "tg"},
                    {"tg_id":3, "tg_value": "po"}]'
;

Online example

You can also do that directly in a HAVING clause if you want

select id
from tag_store
group by id
having jsonb_agg(jsonb_build_object('tg_id', tag_id, 'tg_value', tag_value)) 
        @> '[{"tg_id":1, "tg_value": "cd"},
             {"tg_id":2, "tg_value": "tg"},
             {"tg_id":3, "tg_value": "po"}]'
;

Note that this will return IDs that have additional tags apart from those in the comparison array.

edited Nov 26 '20 at 10:48

answered Nov 26 '20 at 10:37

It looks great brother, what should I change if I have json in a variable @> filter_data::text not working – Md. Parvez Alam Nov 26 '20 at 11:03
If you have a variable in a stored function, then declare it as `jsonb` – Nov 26 '20 at 11:05
Does it required the tag type and tag value in same order... I have two id which has all the tag type and tag values but for one there is an extra in between which is not in filter that id is not being returned – Md. Parvez Alam Nov 26 '20 at 11:15
@Md.ParvezAlam: no, the order of the key/value pairs does not matter. Nor does the order of the items in the array – Nov 26 '20 at 11:54

query for many to many record matching

1 Answers1