Query to extract only records which contains a set of 'values' in jsonb column-CodePudding

I have table which has a column named tags which is a jsonb type column.

                                      Table "public.tagged_products"
   Column    |            Type             |                          Modifiers
------------- ----------------------------- --------------------------------------------------------------
 id          | integer                     | not null default nextval('tagged_products_id_seq'::regclass)
 item_id     | character varying(255)      | not null
 tags        | jsonb                       | not null default '{}'::jsonb
 create_time | timestamp(0) with time zone | not null default now()
 update_time | timestamp(0) with time zone | not null default now()
 active      | boolean                     | not null default true
Indexes:
    "tagged_products_pkey" PRIMARY KEY, btree (id)    "uc_attributes_uid" UNIQUE CONSTRAINT, btree (tags, item_id)    "lots_idx_attr" gin (tags)
    "lots_idx_uid" btree (item_id)

Sample Data

-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id          | 17
item_id     | 9846
tags        | {"coo": "IN", "owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}
create_time | 2022-02-24 11:49:23 05:30
update_time | 2022-02-24 11:49:23 05:30
active      | t

Now I have a set of values which I want to search (not keys because i don't have/know the keys with me before hand) like this:

["IN", "Online"] and both of these needs to be present in the record tags' values.

I referred and tried many things but failed

Referred : How to filter a value of any key of json in postgres

How should I go about writing the query for this use case ?

CodePudding user response：

Assuming your search terms are in a jsonb array, turn it into text[], turn the values from the tags column into text[], and use the @> contains operator:

with search_terms as (
  select array_agg(el) as search_array
    from jsonb_array_elements_text('["IN", "Online"]') as et(el)
)
select m.id, m.tags
  from search_terms s
       cross join mytable m
       cross join lateral jsonb_each_text(tags) t(k,v)
 group by m.id, m.tags, s.search_array
having array_agg(t.v) @> s.search_array
;

Working Fiddle