sql - 使用 Postgres 在 json 数组中索引对象元素

Question

我有一个 9.4 Postgres 数据库，它有一个包含 jsonb 的表字段。

json 的结构类似于:

{
  name: 'customer1',
  age: 28,
  products: [{
    name: 'product1',
    price: 100
  }, {
    name: 'product2',
    price: 200
  }]
}

以下查询返回上述 json 就好了:

SELECT jdoc
FROM customers, jsonb_array_elements(jdoc->'products') as products
WHERE (products->>'price')::numeric > 150

问题是在更大的数据库上性能会受到相当大的影响。

我可以使用什么索引来加速这个查询？

---

我到目前为止所做的尝试:

• GIN指数(jsonb_ops 和 jsonb_path_ops )。它们似乎只适用于像 @> 这样的存在运算符不过。

• CREATE INDEX ON persons(((jsonb_array_elements(jdoc->'products')->>'price')::numeric)) .这给了我数据类型不匹配错误。

  • 请注意 CREATE INDEX ON persons(((jdoc->'age')::numeric))有效并允许我查询 (jdoc->>'age')::numeric < 30快。

Answer 1

您可以创建一个函数并使用它:

CREATE FUNCTION max_price(jsonb) RETURNS double precision
   LANGUAGE sql IMMUTABLE AS
$$SELECT max(p.price)
FROM jsonb_to_recordset($1->'products')
        AS p(name text, price double precision)$$;

CREATE INDEX customers_ind ON customers(max_price(jdoc));

然后这个索引可以和这样的查询一起使用:

EXPLAIN SELECT jdoc FROM customers WHERE max_price(jdoc) > 150;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using customers_ind on customers  (cost=0.12..8.14 rows=1 width=36)
   Index Cond: (max_price(jdoc) > '150'::double precision)
(2 rows)

这不是您的查询，但应该是等价的。