询问
您的表定义丢失。假设：
CREATE TABLE configuration ( config_id serial PRIMARY KEY, config jsonb NOT NULL);
查找
value给定
oid和的a 及其行
instance：
SELECt c.config_id, d->>'value' AS valueFROM configuration c , jsonb_array_elements(config->'data') d -- default col name is "value"WHERe d->>'oid' = '1.3.6.1.4.1.7352.3.10.2.5.35.3'AND d->>'instance' = '0'AND d->>'value' <> '1'
那是一个隐式
LATERAL联接。比较：
- 查询JSON类型内的数组元素
2）什么是得到一个表的3列最快的方法
oid，instance和value.
我想使用jsonb_populate_recordset()
，那么您可以在表定义中提供数据类型。假设
text所有：
CREATE TEMP TABLE data_pattern (oid text, value text, instance text);
也可以是持久化（非临时）表。这仅适用于当前会话。然后：
SELECt c.config_id, d.*FROM configuration c , jsonb_populate_recordset(NULL::data_pattern, c.config->'data') d
就这样。第一个查询重写：
SELECt c.config_id, d.*FROM configuration c , jsonb_populate_recordset(NULL::data_pattern, c.config->'data') dWHERe d.oid = '1.3.6.1.4.1.7352.3.10.2.5.35.3'AND d.instance = '0'AND d.value <> '1';
但这比第一个查询 要慢 。具有更大表的性能的关键是索引支持：
指数
您可以轻松地为问题中建议的标准化（已翻译）表或替代布局编制索引。索引 当前的布局
不是很明显，但是也可以。为了获得最佳性能，我建议
data对
jsonb_path_ops运算符类的键仅使用功能索引。每个文档：
a
jsonb_ops和jsonb_path_opsGIN索引之间的技术区别在于，前者为数据中的每个键和值创建独立的索引项，而后者仅为数据中的每个值创建索引项。
这 应该 对性能产生 奇迹 ：
CREATE INDEX configuration_my_idx ON configurationUSING gin ((config->'data') jsonb_path_ops);
可能希望只有一个完全匹配的JSON数组元素才能起作用，例如：
SELECt * FROM configurationWHERe (config->'data') @> '[{"oid": "1.3.6.1.4.1.7352.3.10.2.5.35.3" , "instance": "0", "value": "1234"}]';注意JSON数组符号（与 包封[]
）所提供的值，这是所需要的。
但是带有 键子集的 数组元素也可以工作：
SELECt * FROM configurationWHERe (config->'data') @> '[{"oid": "1.3.6.1.4.1.7352.3.10.2.5.35.3" , "instance": "0"}]'困难的部分是合并您看似毫无疑问的添加谓词
value <> '1'。必须注意将所有谓词应用于 同一 数组元素。您可以将其与第一个查询结合使用：
SELECt c.*, d->>'value' AS valueFROM configuration c , jsonb_array_elements(config->'data') dWHERe (config->'data') @> '[{"oid": "1.3.6.1.4.1.7352.3.10.2.5.35.3", "instance": "0"}]'AND d->>'oid' = '1.3.6.1.4.1.7352.3.10.2.5.35.3' -- must be repeatedAND d->>'instance' = '0' -- must be repeatedAND d->>'value' <> '1' -- here we can rule outVoilá。
特殊指标
如果表很大，则索引大小可能是决定因素。您可以将此特殊解决方案的性能与功能索引进行比较：
此函数从给定值提取Postgres oid-instance 组合数组
jsonb：
CREATE OR REPLACe FUNCTION f_config_json2arr(_j jsonb) RETURNS text[] LANGUAGE sql IMMUTABLE AS$func$SELECT ARRAY( SELECT (elem->>'oid') || '-' || (elem->>'instance') FROM jsonb_array_elements(_j) elem )$func$
我们可以基于此构建功能索引：
CREATE INDEX configuration_conrfig_special_idx ON configurationUSING gin (f_config_json2arr(config->'data'));
并基于此查询：
SELECt * FROM configurationWHERe f_config_json2arr(config->'data') @> '{1.3.6.1.4.1.7352.3.10.2.5.35.3-0}'::text[]这个想法是索引应该小得多，因为它只存储组合值而没有键。的 阵列 容纳操作者@>
本身应该执行类似于jsonb容纳操作者@>
。我希望不会有太大的区别，但是我会很感兴趣，这更快。
类似于此相关答案中的第一个解决方案（但更具体）：
- 在JSON数组中查找元素的索引
助手：
- 我不会将其
oid
用作列名，因为在Postgres中它也用于内部目的。 - 如果可能，我将使用不带JSON的普通标准化表。
