Postgres 9.4 массив jsonb как таблица

Запрос

Отсутствует определение таблицы. Предполагая:

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) Какой самый быстрый способ получить таблицу с 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') 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. согласно документации:

Техническая разница между jsonb_ops и jsonb_path_ops джином индекс заключается в том, что первый создает независимые элементы индекса для каждого ключа и значение в данных, в то время как последний создает элементы индекса только для каждое значение в данных.

Это должны творить чудеса для исполнения:

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

Вуаля.

Специальный индекс

Если ваша таблица огромна, размер индекса может быть решающим фактором. Вы можете сравнить производительность этого специального решения с функциональным индексом:

Эта функция извлекает массив PostgresOID-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'));

И основываем на нем запрос:

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.