postgresql - 使用 Postgres 索引嵌套的 json

Question

我们有一个带有 jsonb 字段的表，其内容如下:

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

我们想创建一个索引，以便我们可以通过 uri 快速查找记录。我们如何创建一个？如果 terms 是一个数组而不是对象，会有帮助吗？

Answer 1

这可以满足您的要求，但可能不够通用，我还是会发布它。

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 键值是一个数组它只放置第一个元素(需要修复))

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