PostgreSQL，从JSON数组中检索特定键的值

Question

我有对象的Postgres JSONB数组，看起来像这样：

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

此JSONB是一个函数参数。

什么是最有效的检索skillLevel方法 skillId = "1"。

我尝试过玩，jsonb_array_elements但是到目前为止我所做的一切看起来都非常混乱。

Answer 1

在 Postgres 9.4+中 ，可以jsonb_array_elements()在横向 联接中 使用该函数：

select (elem->>'skillLevel')::int as skill_level
from my_table
cross join jsonb_array_elements(json_col) elem
where elem->>'skillId' = '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;

在 Postgres 12+中， 您可以使用jsonb路径函数的形式来替代：

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

Db
<>提琴。

阅读有关JSON函数和运算符的更多信息。