PostgreSQL，从JSON数组中检索特定键的值 [英] Postgresql, retrieve value for specific key from json array

问题描述

我有Postgres JSONB对象数组，如下所示:

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

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

此JSONB是一个函数参数.

This JSONB is a function argument.

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

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

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

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

推荐答案

在 Postgres 9.4 + 中，在横向联接中使用函数jsonb_array_elements():

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;

在 Postgres 12 + 中，您可以通过jsonb路径函数的形式找到一个不错的选择:

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<>提琴.

详细了解 JSON函数和运算符.

这篇关于PostgreSQL，从JSON数组中检索特定键的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！