Postgres 12

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?.

drop table if exists my_table;
create table my_table(id serial primary key, json_col jsonb);
insert into my_table (json_col) values
('[
  {
    "skillId": "1",
    "skillLevel": 42
  },
  {
    "skillId": "2",
    "skillLevel": 41
  }
]');

DROP TABLE

CREATE TABLE

INSERT 0 1

select (elem->>'skillLevel')::int as skill_level
from my_table
cross join jsonb_array_elements(json_col) elem
where elem->>'skillId' = '1';

skill_level

42

SELECT 1

create or replace function extract_skill_level(json_data jsonb, id int)
returns integer language sql as $$
    select (elem->>'skillLevel')::int
    from jsonb_array_elements(json_data) elem
    where elem->>'skillId' = id::text
$$;

select extract_skill_level(json_col, 1) as skill_level
from my_table;

CREATE FUNCTION

skill_level

42

SELECT 1

select (
    jsonb_path_query(
        json_col, 
        '$[*] ? (@.skillId == "1")'
        )->'skillLevel'
    )::int as skill_level
from my_table;

skill_level

42

SELECT 1