使用Postgres过滤JSONB数据

Question

使用Postgres过滤JSONB数据

3

假设你有一个网站，人们在其中发布广告。每个广告都有一些已选属性，例如汽车具有不同的发动机类型、齿轮、颜色等。用户在提交列表之前选择这些属性。我将所选属性以jsonb格式存储在列表表中，查看数据列： link to image

。因此，每个列表都包含此类数据：

{
   "properties":[
      {
         "id":"1",
         "value_id":"1"
      },
      {
         "id":"2",
         "value_id":"5"
      },
      {
         "id":"3",
         "value_id":"9"
      },
      {
         "id":"4",
         "value":"2.0"
      },
      {
         "id":"7",
         "value":"2017"
      },
      {
         "id":"6",
         "value":"180.000"
      }
   ]
}

现在的问题是：

1）如何按照 JSON 中的 ID 和值筛选列表？例如，显示 ID = 2 且其值等于 5，ID = 3 且其值等于 9 等。我不需要OR，我需要AND。因此，通过多个 ID 和值进行过滤数据。

2）第一个点 + 比较 ID 和值的能力（大于或小于）。

- Artur Nazarov

1

从示例看来，您正在将应该存储在单独表中的内容存储在jsonb中。 - Justinas Marozas

@Justinas Marozas，我在另一个表中有这些属性的翻译和其他元数据。这是因为网站有多种语言。我会将这些值和ID与其他表进行JOIN操作。 - Artur Nazarov

但是你为什么要使用具有常规结构的数据的jsonb呢？为什么不用一个单独的三列表（包括外键）呢？或者甚至是一个jsonb数组，例如array['{"id":"1","value_id":"1"}'::jsonb, '{"id":"2","value_id":"5"}', ...]？ - mu is too short

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vao Tsun · Answer 1

回答第一个问题，这可能是我第一次发现jsonb[]有用的时候：

t=# with c(a,j) as (values(18,'{
   "properties":[
      {
         "id":"1",
         "value_id":"1"
      },
      {
         "id":"2",
         "value_id":"5"
      },
      {
         "id":"3",
         "value_id":"9"
      },
      {
         "id":"4",
         "value":"2.0"
      },
      {
         "id":"7",
         "value":"2017"
      },
      {
         "id":"6",
         "value":"180.000"
      }
   ]
}'::jsonb), (19,'{"properties":[{"id": "1", "value_id": "1"}]}'))
, m as (select a, array_agg(jb.value)::jsonb[] ar from c, jsonb_array_elements(j->'properties') jb group by a)
select a 
from m 
where '{"id": "1", "value_id": "1"}'::jsonb = any(ar) 
  and '{"id": "3", "value_id": "9"}'::jsonb = any(ar);
 a
----
 18
(1 row)

至于第二个要求——它不会那么简短，因为您需要进行比较（因此需要解析JSON）：

t=# with c(a,j) as (values(18,'{
   "properties":[
      {
         "id":"1",
         "value_id":"1"
      },
      {
         "id":"2",
         "value_id":"5"
      },
      {
         "id":"3",
         "value_id":"9"
      },
      {
         "id":"4",
         "value":"2.0"
      },
      {
         "id":"7",
         "value":"2017"
      },
      {
         "id":"6",
         "value":"180.000"
      }
   ]
}'::jsonb), (19,'{"properties":[{"id": "1", "value_id": "1"}]}'))
, m as (select a, jb.value->>'id' id,jb.value->>'value_id' value_id from c, jsonb_array_elements(j->'properties') jb)
, n as (select m.*, count(1) over (partition by m.a)
from m
join c on c.a = m.a and ((id::int >= 1 and value_id::int <2) or (id::int >2 and value_id::int <= 9)))
select distinct a from n
where count > 1;
 a
----
 18
(1 row)

使用基本思路是使用OR获取可能的行，然后检查是否满足所有OR条件