I have table tag_store like below
I want to filter the ids which has all tag provided in a like
x
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
Advertisement
Answer
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"}]'
;
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.