CODEWITHSUNDEEP
postgresqlindexingjsonb
Michiel Borkent
Michiel Borkent

Reputation: 34840

Indexing nested json with Postgres

We have a table with a jsonb field with content like this:

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

We would like to create an index so we can quickly find records by uri. How do we create one? Would it help if terms was an array instead on object?

Upvotes: 4

Views: 1776

Answers (1)

cske
cske

Reputation: 2243

This does what you want, but may not be generic enough, I'll post it anyway.

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[])"

With help of Collect Recursive JSON Keys In Postgres i come up with this

Updated trigger it will find keys uri at any depth and extract values to urls column for indexing (if uri keys value is an array it only puts the first element (fix required))

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;

Result 

"{bar3,bar,foo,bar2}"

Upvotes: 4

Related Questions