Postgres 9.4 jsonb array as table

Question

I have a json array with about 1000 elements of the structure "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"}]}

oidand instanceare unique to each json array. If I was given the opportunity to change the structure, I would change the format to a 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"}

However, if I need to stay with the old structure

• What is the fastest way to get specific oidfrom an array?

• What is the fastest way to get a table with three columns oid, instanceand value. Or even better is a pivot table with an oid + instance as the column header.

For 2. I tried the following, but on a large table it was rather slow:

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';

Answer 1

Query

. , :

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

a value oid 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'

LATERAL. :

• JSON-

2) oid, instance value.

jsonb_populate_recordset(), . text :

CREATE TEMP TABLE data_pattern (oid text, value text, instance text);

(non-temp) . . :

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 jsonb_path_ops. :

a jsonb_ops a jsonb_path_ops GIN , , .

:

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"}]'

, , , value <> '1'. . :

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- 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'));

:

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

, , . @> jsonb @>. , , .

( ):

• JSON

Asides:

• oid , Postgres.
• , , JSON.