Postgres JSONB Query and Index on Nested String Array

Question

我在如何制定查询并为以下情况提供适当的索引方面遇到了一些麻烦。我有这样的 JSON 表示的客户实体(仅保留相关属性(：

{
"id": "50000",
"address": [
{
"line": [
"2nd Main Street",
"123 Harris Plaza"
],
"city": "Boston",
"state": "Massachusetts",
"country": "US",
},
{
"line": [
"1st Av."
],
"city": "Jamestown",
"state": "Massachusetts",
"country": "US",
}
]

}

客户存储在以下客户表中：

CREATE TABLE Customer (
id BIGSERIAL PRIMARY KEY,
resource JSONB

(;

我设法对资源列进行简单的查询，例如像这样的投影查询(检索以"bo"开头的城市的所有小写地址行(：

SELECT LOWER(jsonb_array_elements_text(jsonb_array_elements(c.resource#>'{address}') #> '{line}')) FROM Customer c, jsonb_array_elements(c.resource #> '{address}') a WHERE LOWER(a->>'city') LIKE 'bo%';

我在执行以下操作时遇到问题：我的目标是查询至少有一个地址行以"12"开头的所有客户。不区分大小写是我的用例的要求。示例客户将与我的查询匹配，因为第一个地址对象的地址行以"12"开头。请注意，"line"是JSON字符串数组，而不是复杂对象。到目前为止，我能想到的最接近的事情是：

SELECT c.resource FROM Customer c, jsonb_array_elements(c.resource #> '{address}') a WHERE a->'line' ?| array['123 Harris Plaza'];

显然，这不是不区分大小写的 LIKE 查询。非常感谢有关如何制定查询和随附的GIN索引的任何帮助/指示。我的第一个查询已经将所有地址行选择为文本，所以也许这可以用于 GIN 索引？

我正在使用Postres 9.5，但如果这只能在更新的Postgres版本中实现，我很乐意升级。

Answer 1

虽然 GIN 索引具有支持前缀匹配的机制，但此机制仅与 tsvector 挂钩。 array_ops没有把它勾起来，json_ops也没有json_path_ops。 因此，除非你想创建新的运算符类/族(或将数据规范化到单独的表中(，否则你将不得不将数据硬塞进 tsvector 中。

这是一种粗略的方法，它不考虑地址行可能包含文字单引号或其他有意义的字符的可能性：

create function addressline_tsvector(jsonb) returns tsvector immutable language SQL as $$ 
select string_agg('''' || lower(value) || '''', ' ')::tsvector 
from jsonb_array_elements($1->'address') a(a),
jsonb_array_elements_text(a->'line') 
$$;
create index on customer using gin (addressline_tsvector(resource));
select * from customer where addressline_tsvector(resource) @@ lower('''2nd Main'':*')::tsquery;

鉴于您的示例表只有一行，除非您先set enable_seqscan = off，否则可能不会实际使用该索引。