sql – Postgres 9.4 jsonb数组作为表

{"_id": 37637070,"data": [{"oid": "11.5.15.1.4","value": "1","instance": "1.1.4"},{"oid": "11.5.15.1.9","value": "17",{"oid": "12.5.15.1.5","value": "0.0.0.0","instance": "0"}]}

每个json数组的oid和instance都是唯一的.如果我可以选择更改结构,我会将格式更改为key：value：

{"11.5.15.1.4-1.1.4":"1","11.5.15.1.9-1.1.4": "17","12.5.15.1.5-0": "0.0.0.0"}

但是,如果我需要留在旧结构

>从阵列中获取特定oid的最快方法是什么？
>获取包含3列oid,instance和value的表的最快方法是什么.或者更好的是使用oid实例作为列标题的数据透视表.

对于2.我尝试了以下内容,但在大型桌面上它很慢：

select *
from (
   select a->>'oid' oid,a->>'instance' instance,a->>'value' value1,id
   from (
      select jsonb_array_elements(config#>'{data}')  a,id
      from configuration
      ) b
   ) c
where  oid = '1.3.6.1.4.1.7352.3.10.2.5.35.3' and instance = '0' and value1 <> '1';

解决方法

您的表定义丢失了.假设：

CREATE TABLE configuration (
  config_id serial PRIMARY KEY,config jsonb NOT NULL
);

要查找给定oid和实例的值及其行：

SELECT c.config_id,d->>'value' AS value
FROM   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连接.比较：

> Query for array elements inside JSON type

2) What is the fastest way to get a table with 3 columns of oid,instance and value.

我想使用jsonb_populate_recordset(),然后你可以在表定义中提供数据类型.假设所有人的文字：

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,c.config->'data') d
WHERE  d.oid      = '1.3.6.1.4.1.7352.3.10.2.5.35.3'
AND    d.instance = '0'
AND    d.value   <> '1';

但这比第一个查询要慢.使用更大表的性能关键是索引支持：

指数

您可以轻松索引标准化(已翻译)表或您在问题中提出的替代布局.索引当前布局不是那么明显,但也可能.为了获得最佳性能,我建议使用jsonb_path_ops运算符类对数据键进行功能索引. Per documentation:

The technical difference between a jsonb_ops and a jsonb_path_ops GIN
index is that the former creates independent index items for each key
and value in the data,while the latter creates index items only for
each value in the data.

这应该是性能的奇迹：

CREATE INDEX configuration_my_idx ON configuration
USING gin ((config->'data') jsonb_path_ops);

人们可能期望只有JSON数组元素的完全匹配才能起作用,例如：

SELECT * FROM configuration
WHERE  (config->'data') @> '[{"oid": "1.3.6.1.4.1.7352.3.10.2.5.35.3","instance": "0","value": "1234"}]';

请注意所提供的值的JSON数组符号(带有[]).

但是具有键子集的数组元素也可以工作：

SELECT * FROM configuration
WHERE  (config->'data') @> '[{"oid": "1.3.6.1.4.1.7352.3.10.2.5.35.3","instance": "0"}]'

困难的部分是结合你看似不可思议的添加谓词值<> ‘1’.必须小心将所有谓词应用于同一个数组元素.您可以将其与第一个查询结合使用：

SELECT c.*,jsonb_array_elements(config->'data') d
WHERE  (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 repeated
AND    d->>'instance' = '0'                               -- must be repeated
AND    d->>'value'   <> '1'                               -- here we can rule out

瞧.

特别指数

如果您的表很大,索引大小可能是决定因素.您可以将此特殊解决方案的性能与功能索引进行比较：

此函数从给定的jsonb值中提取Postgres的oid-instance组合数组：

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 configuration
USING  gin (f_config_json2arr(config->'data'));

并将查询基于它：

SELECT * FROM configuration
WHERE  f_config_json2arr(config->'data') @> '{1.3.6.1.4.1.7352.3.10.2.5.35.3-0}'::text[]

我们的想法是索引应该大得多,因为它只存储没有键的组合值. array containment operator @>本身应该与jsonb containment operator @>类似.我不希望有太大的区别,但我会非常感兴趣哪个更快.

与此相关答案中的第一个解决方案类似(但更专业)：

> Index for finding an element in a JSON array

旁白：

>我不会使用oid作为列名,因为它也用于Postgres的内部用途.>如果可能的话,我会使用没有JSON的普通规范化表.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!