postgresql - 在 Postgresql 中从 JSONB 中提取值

Question

我有一个表(用户)，我在其中存储不同的列，其中一个是名为 content 的 jsonb 列。其他列无关紧要，因为它们指的是日期和其他不相关的内容。在那个 jsonb 列中，我们存储了一个具有以下语法的文件:

{
 "Root": {
    "Users": {
        "user1": {
            "Email": {
                "_value": "user1@email.com"
            },
            "FullName": {
                "_value": "User1"
            },
            "Teams": {
                "_value": "TeamA, TeamB"
            },
        "user2": {
            "Email": {
                "_value": "user2@email.com"
            },
            "FullName": {
                "_value": "User2"
            },
            "Teams": {
                "_value": "TeamA, TeamB, TeamC"
            }, 
.... 

我想要实现的目标是:从这个 jsonb 中提取所有用户以及表中的每个参数，应该如下所示:

username |      email    |  fullname   |   teams
user1    |user1@email.com|  User1      | TeamA, TeamB
user2    |user2@email.com|  User2      | TeamA, TeamB, TeamC

我尝试使用 jsonb_object_keys(content->'Root'->'users') 并设法提取所有用户，但似乎无法在树中继续提取每个值每个用户。我被封锁了，似乎找不到适合我的东西。最终目标是根据我提供的团队参数提取具有所述详细信息的每个用户，因此我将把所有内容都放在一个函数中。我继承了这个结构，同时我是使用 jsonb general 的新手。即使是可以以某种方式扁平化的东西，这个 jsonb 也会很棒。

使用的 PostgreSQL 版本:9.5

如果有人能提供一些意见，我们将不胜感激。谢谢。

Answer 1

teams 是作业，其余示例如下:

t=# with o as (with j as (select '
{
 "Root": {
    "Users": {
        "user1": {
            "Email": {
                "_value": "user1@email.com"
            },
            "FullName": {
                "_value": "User1"
            },
            "Teams": {
                "_value": "TeamA, TeamB"
            }
        },
        "user2": {
            "Email": {
                "_value": "user2@email.com"
            },
            "FullName": {
                "_value": "User2"
            },
            "Teams": {
                "_value": "TeamA, TeamB, TeamC"
            }
        }
    }
  }
}'::jsonb v)
select key,value from j join jsonb_each(j.v->'Root'->'Users') on true) select key username, value->'Email'->>'_value' email, value->'FullName'->>'_value' fullname from o;
 username |      email      | fullname
----------+-----------------+----------
 user1    | user1@email.com | User1
 user2    | user2@email.com | User2
(2 rows)