查询仅提取包含一组＆＃x27; value＆＃x27的记录。在JSONB专栏中

Question

我有一个名为tags的列，该列是JSONB类型列。

                                      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)

示例数据

-[ 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

现在我有一组我想搜索的值（不是键，因为我没有/不知道我的钥匙了）这样的值：

[in“ in”，“ inloine”]这些都需要在记录标签的值中存在。

我转介并尝试了很多事情，但失败了

：如何过滤postgres中JSON的任何键的值

我应该如何编写此用例的查询？

Answer 1

假设您的搜索术语在jsonb数组中，将其转换为text []，将值从tags列转换为text> text> text []，并使用@＆gt;包含操作员：

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
;

工作小提琴

Answer 2

示例 by

select m.id, m.tags
  from mytable m
       cross join lateral 
       (SELECT array_agg(t.v) AS v
       FROM jsonb_each_text(tags) t(k,v)) AS t
WHERE t.v @> '{"IN", "Online"}'

基于示例 @mikeorganek