sql - Postgres 9.4 jsonb数组作为表

sql - Postgres 9.4 jsonb数组作为表,第1张

我有一个json数组,其中包含大约1000个结构元素" oid:aaa,instance:bbb,value:ccc"。

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

oidinstance是唯一的。如果我可以选择更改结构,我会将格式更改为 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"}

但是,如果我需要继续使用旧结构

  1. 从阵列中获取特定oid的最快方法是什么?

  2. 获得包含3列oidinstancevalue的表格的最快方法是什么。或者更好的是使用oid instance作为列标题的数据透视表。

  3. 对于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';
    

    最佳答案:

    1 个答案:

    答案 0 :(得分:6)

    查询

    您的表定义丢失了。假设:

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

    要查找给定valueoid的{​​{1}}及其行:

    instance

    这是一个隐含的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' 联接。比较:

    • Query for array elements inside JSON type
      

    2)获得包含3列LATERALoidinstance

    的表格的最快方法是什么?

    我想使用jsonb_populate_recordset(),然后您可以在表定义中提供数据类型。假设所有人都value.

    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') 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'; 运算符类的data键的功能索引。 Per documentation:

      

    jsonb_path_opsjsonb_ops GIN之间的技术差异   index是前者为每个键创建独立索引项   和数据中的值,而后者仅为其创建索引项   数据中的每个值。

    这个应该可以创造奇迹

    jsonb_path_ops

    有人可能会认为只有JSON数组元素的完全匹配才能起作用,例如:

    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"}]'; ),这是必需的。

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

    []

    困难的部分是合并你看似不吉利的添加谓词SELECT * FROM configuration WHERE (config->'data') @> '[{"oid": "1.3.6.1.4.1.7352.3.10.2.5.35.3" , "instance": "0"}]' 。必须注意将所有谓词应用于相同的数组元素。您可以将其与第一个查询结合使用:

    value <> '1'

    VOILÀ。

    特殊索引

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

    此函数从给定的SELECT c.*, d->>'value' AS value FROM configuration 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 值中提取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 configuration
    USING  gin (f_config_json2arr(config->'data'));
    

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

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

    • Index for finding an element in a JSON array

    旁白:

    • 我不会使用SELECT * FROM configuration WHERE f_config_json2arr(config->'data') @> '{1.3.6.1.4.1.7352.3.10.2.5.35.3-0}'::text[] 作为列名,因为它也用于Postgres的内部用途。
    • 如果可能,我会使用没有JSON的普通规范化表。
    本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复