Postgresql, retrieve value for specific key from json array

Question

I have Postgres JSONB array of objects, looking like this :

'[
  {
    "skillId": "1",
    "skillLevel": 42
  },
  {
    "skillId": "2",
    "skillLevel": 41
  }
]'

This JSONB is a function argument.

What is the most efficient way to retrieve skillLevel for skillId = "1".

I've tried to play with jsonb_array_elements but everything I've done so far is looking really messy.

Answer 1

In Postgres 9.4+ use the function jsonb_array_elements() in a lateral join:

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

You can implement the idea in a simple function, e.g:

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;

In Postgres 12+ you have a nice alternative in the form of jsonb path functions:

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

Db<>Fiddle.

Read more about JSON Functions and Operators.