Индексирование вложенного json с помощью Postgres

У нас есть таблица с полем jsonb с таким содержимым:

{"terms": {
  "foo": {
    "uri": "foo"
  },
  "bar": {
    "uri": "bar"
  }
 }
}

Мы хотели бы создать индекс, чтобы мы могли быстро находить записи по uri. Как мы его создаем? Помогло бы, если бы terms был массивом, а не объектом?


postgresql indexing jsonb
person Michiel Borkent    schedule 18.10.2016    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Это делает то, что вы хотите, но может быть недостаточно общим, я все равно опубликую его.

CREATE TABLE public.jin2 (
   id BIGINT
  ,d  JSONB
  ,urls  text[]
);

CREATE OR REPLACE FUNCTION public.extract_urls() RETURNS TRIGGER AS $$
    BEGIN
      with data(json_value) as (
        values (NEW.d)
      )
      select ARRAY_AGG(j2.value)
      from data,
        lateral jsonb_each(json_value->'terms') j1,
        lateral jsonb_each_text(value) j2
      where j2.key = 'uri' into NEW.urls;
      RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER extract_trigger BEFORE INSERT OR UPDATE ON public.jin2 FOR EACH ROW EXECUTE PROCEDURE extract_urls();

CREATE INDEX idx_test on  public.jin2 USING GIN (urls);

INSERT INTO public.jin2(id,d) VALUES (1,'{"terms": {
  "foo": {
    "uri": "foo"
  },
  "bar": {
    "uri": "bar"
  }
 }
}'::jsonb);

INSERT INTO public.jin2(id,d) VALUES (2,'{"terms": {
  "foo": {
    "uri": "foo2"
  },
  "bar": {
    "uri": "bar"
  }
 }
}'::jsonb);

SET enable_seqscan TO off;
EXPLAIN  select * from public.jin2 where urls @> ARRAY['foo']

Bitmap Heap Scan on jin2  (cost=8.00..12.01 rows=1 width=72)
  Recheck Cond: (urls @> '{foo}'::text[])
  ->  Bitmap Index Scan on idx_test  (cost=0.00..8.00 rows=1 width=0)
        Index Cond: (urls @> '{foo}'::text[])"

С помощью Collect Recursive JSON Keys In Postgres я придумал это

Обновлен триггер, он будет находить ключи uri на любой глубине и извлекать значения в столбец urls для индексации (если значение uri keys является массивом, он помещает только первый элемент (требуется исправление))

CREATE OR REPLACE FUNCTION public.extract_urls() RETURNS TRIGGER AS $$
    BEGIN

      WITH RECURSIVE doc_key_and_value_recursive(key, value) AS (
        SELECT
          t.key,
          t.value
        FROM  jsonb_each(NEW.d) AS t

        UNION ALL

        SELECT
          t.key,
          t.value
        FROM doc_key_and_value_recursive,
          jsonb_each(CASE
                     WHEN jsonb_typeof(doc_key_and_value_recursive.value) <> 'object' THEN '{}' :: JSONB
                     ELSE doc_key_and_value_recursive.value
                     END) AS t
      )
      SELECT ARRAY_AGG(value->>0)
      FROM doc_key_and_value_recursive
      WHERE jsonb_typeof(doc_key_and_value_recursive.value) NOT IN ('array', 'object')
            and key = 'uri' into NEW.urls;

      RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

INSERT INTO public.jin2(id,d) VALUES (104,'{"terms": {
  "foo": {
    "uri": "foo"
    ,"other":"buzz"
  },
  "bar": {
    "uri": "bar"
    ,"deeper": {
      "uri": "bar2"
    }
  }
  ,"uri": "bar3"
  ,"and": {"uri": ["bar3","bar4"]}
 }
}'::jsonb);
select urls from public.jin2 where id=104;

Результат

"{bar3,bar,foo,bar2}"
person cske    schedule 18.10.2016