Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE users(id int, experience jsonb);
INSERT INTO users VALUES
  (1,  jsonb '[
    {
        "field": "devops",
        "years": 9
    },
    {
        "field": "backend dev",
        "years": 7
    } 
   ]')
, (2,  jsonb '[
    {
        "field": "devops",
        "years": 6
    },
    {
        "field": "java",
        "years": 7
    },
    {
        "field": "ui/ux",
        "years": 3
    }
   ]');

CREATE INDEX users_exp_gin_idx ON users USING gin (experience jsonb_path_ops);

CREATE TABLE



INSERT 0 2



CREATE INDEX


-- org
SELECT * FROM users
WHERE  jsonb_path_exists(experience, '$[*] ? (@.field == "devops" && @.years > 5)')
AND    jsonb_path_exists(experience, '$[*] ? (@.field == "backend dev" && @.years > 5)')
LIMIT  3;


id
experience


1
[{"field": "devops", "years": 9}, {"field": "backend dev", "years": 7}]



SELECT 1


-- equivalent, better:
SELECT *
FROM   users
WHERE  experience @? '$[*] ? (@."field" == "devops" && @.years > 5)'
AND    experience @? '$[*] ? (@.field == "backend dev" && @.years > 5)'
LIMIT  3;


id
experience


1
[{"field": "devops", "years": 9}, {"field": "backend dev", "years": 7}]



SELECT 1


SET enable_seqscan = OFF;  --  to show if index is applicable

SET


-- org
EXPLAIN
SELECT * FROM users
WHERE  jsonb_path_exists(experience, '$[*] ? (@.field == "devops" && @.years > 5)')
AND    jsonb_path_exists(experience, '$[*] ? (@.field == "backend dev" && @.years > 5)')
LIMIT  3;


QUERY PLAN


Limit  (cost=10000000000.00..10000000001.03 rows=1 width=36)

  ->  Seq Scan on users  (cost=10000000000.00..10000000001.03 rows=1 width=36)

        Filter: (jsonb_path_exists(experience, '$[*]?(@."field" == "devops" && @."years" > 5)'::jsonpath, '{}'::jsonb, false) AND jsonb_path_exists(experience, '$[*]?(@."field" == "backend dev" && @."years" > 5)'::jsonpath, '{}'::jsonb, false))



EXPLAIN


-- better (uses index!)
EXPLAIN
SELECT *
FROM   users
WHERE  experience @? '$[*] ? (@."field" == "devops" && @.years > 5)'
AND    experience @? '$[*] ? (@.field == "backend dev" && @.years > 5)'
LIMIT  3;


QUERY PLAN


Limit  (cost=12.00..16.02 rows=1 width=36)

  ->  Bitmap Heap Scan on users  (cost=12.00..16.02 rows=1 width=36)

        Recheck Cond: ((experience @? '$[*]?(@."field" == "devops" && @."years" > 5)'::jsonpath) AND (experience @? '$[*]?(@."field" == "backend dev" && @."years" > 5)'::jsonpath))

        ->  Bitmap Index Scan on users_exp_gin_idx  (cost=0.00..12.00 rows=1 width=0)

              Index Cond: ((experience @? '$[*]?(@."field" == "devops" && @."years" > 5)'::jsonpath) AND (experience @? '$[*]?(@."field" == "backend dev" && @."years" > 5)'::jsonpath))



EXPLAIN


-- generate query dynamically
SELECT 'SELECT * FROM users
WHERE  experience @? '
       || string_agg(quote_nullable(format('$[*] ? (@.field == %s && @.years > %s)'
                                         , f->'field'
                                         , f->'years')) || '::jsonpath'
                   , E'\nAND    experience @? ')
       || E'\nLIMIT  3'
FROM   jsonb_array_elements('[{"field": "devops", "years": 5 }, 
                              {"field": "java", "years": 6 }, 
                              {"field": "ui/ux", "years": 2 }]') f;


?column?


SELECT * FROM users
WHERE  experience @? '$[*] ? (@.field == "devops" && @.years > 5)'::jsonpath
AND    experience @? '$[*] ? (@.field == "java" && @.years > 6)'::jsonpath
AND    experience @? '$[*] ? (@.field == "ui/ux" && @.years > 2)'::jsonpath
LIMIT  3



SELECT 1


-- execute generated query manually:
SELECT * FROM users
WHERE  experience @? '$[*] ? (@.field == "devops" && @.years > 5)'::jsonpath
AND    experience @? '$[*] ? (@.field == "java" && @.years > 6)'::jsonpath
AND    experience @? '$[*] ? (@.field == "ui/ux" && @.years > 2)'::jsonpath
LIMIT  3;


id
experience


2
[{"field": "devops", "years": 6}, {"field": "java", "years": 7}, {"field": "ui/ux", "years": 3}]



SELECT 1


-- FULL automation
CREATE OR REPLACE FUNCTION f_users_with_experience(_filter_arr jsonb, _limit int = 3)
  RETURNS SETOF users
  LANGUAGE plpgsql PARALLEL SAFE STABLE STRICT AS
$func$
DECLARE
   _sql text;
BEGIN
   -- assert (you may want to be stricter?)
   IF jsonb_path_exists (_filter_arr, '$[*] ? (!exists(@.field) || !exists(@.years))') THEN
      RAISE EXCEPTION 'Parameter $2 (_filter_arr) must be a JSON array with keys "field" and "years" in every object. Invalid input was: >>%<<', _filter_arr;
   END IF;

   -- generate query string
   SELECT INTO _sql
          'SELECT * FROM users
WHERE  experience @? '
       || string_agg(quote_nullable(format('$[*] ? (@.field == %s && @.years > %s)'
                                         , f->'field'
                                         , f->'years'))
                   , E'\nAND    experience @? ')
       || E'\nLIMIT   ' || _limit
   FROM   jsonb_array_elements(_filter_arr) f;

   -- execute
   IF _sql IS NULL THEN
      RAISE EXCEPTION 'SQL statement is NULL. Should not occur!';
   ELSE
   -- RAISE NOTICE '%', _sql;     -- debug first if in doubt
      RETURN QUERY EXECUTE _sql;
   END IF;
END
$func$;

CREATE FUNCTION


-- valid call (excess keys are ignored)
SELECT * FROM f_users_with_experience('[
        {"field": "devops", "years": 5 }, 
        {"field": "backend dev", "years": 6, "foo":"bar"}]');


id
experience


1
[{"field": "devops", "years": 9}, {"field": "backend dev", "years": 7}]



SELECT 1


-- illegal call (missing key)
SELECT * FROM f_users_with_experience('[
        {"field": "devops", "years": 5 }, 
        {"field": "backend dev", "wrong_key": 6 }]')

ERROR:  Parameter $2 (_filter_arr) must be a JSON array with keys "field" and "years" in every object. Invalid input was: >>[{"field": "devops", "years": 5}, {"field": "backend dev", "wrong_key": 6}]<<
CONTEXT:  PL/pgSQL function f_users_with_experience(jsonb,integer) line 7 at RAISE


-- illegal call (not a JSON array)
SELECT * FROM f_users_with_experience('{"field": "devops", "years": 5}')

ERROR:  cannot extract elements from an object
CONTEXT:  SQL statement "SELECT                     'SELECT * FROM users
WHERE  experience @? '
       || string_agg(quote_nullable(format('$[*] ? (@.field == %s && @.years > %s)'
                                         , f->'field'
                                         , f->'years'))
                   , E'\nAND    experience @? ')
       || E'\nLIMIT   ' || _limit
   FROM   jsonb_array_elements(_filter_arr) f"
PL/pgSQL function f_users_with_experience(jsonb,integer) line 11 at SQL statement