postgresql JOIN其他表与jsonb数据类型在postgres内的对象本身

l7wslrjt 于 2023-06-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(178)

我想要连接的表的表模式是：-

uuid uuid NOT NULL DEFAULT gen_random_uuid(),
    actor_uuid uuid NOT NULL,
    ref_uuid uuid NOT NULL,
    ref_type character varying(255) COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp with time zone NOT NULL,
    activity jsonb NOT NULL,
    CONSTRAINT activity_log_pkey PRIMARY KEY (uuid)

在活动列中，数据将类似于：-

[{"type": "USER", 
"uuid": "6ae0fa04-804d-492a-94fa-ccf75556379f"}, 
{"type": "MESSAGE", 
"message": "activity_template.collection_details.updated"}, 
{"type": "COLLECTION", "uuid": "914f4c89-1857-4fbe-aa3e-436e343b42f2"}]

我想在一个特定的对象中连接我的用户表，其中类型为USER，并且对于COLLECTION也是如此
请告诉我如何加入表并在某个地方得到响应，如

[
  {
    activity: [
       {
        type: "USER",
        uuid: "6ae0fa04-804d-492a-94fa-ccf75556379f",  
        first_name: "Henil",
        last_name: "Mehta"
       },
       {
        type: "MESSAGE",
        
        name: "Netflix"
       },
       {
        type: "COLLECTION",
       uuid: "7qe0fa04-804d-492a-94fa-ccf75556379f",   
        title: "Netflix"
       },
    ] 
  }
]

postgresql

来源：https://stackoverflow.com/questions/76381424/join-other-tables-with-jsonb-data-type-in-postgres-inside-that-object-itself

1条答案

按热度按时间

zzlelutf1#

with user_col (id, userId, collectionId) as (select id
                                                  , cast(
                jsonb_path_query_first(activity, '$[*] ? (@.type == "USER")') #>> '{uuid}' as uuid)       as userId
                                                  , cast(
                jsonb_path_query_first(activity, '$[*] ? (@.type == "COLLECTION")') #>>
                '{uuid}' as uuid)                                                                         as collectionId
                                             from t1),
     basedata as
         (select userId,
                 collectionId,
                 first_name,
                 last_name,
                 title
          from user_col uc
                   left join users u on uc.userId = u.id
                   left join collections c on uc.collectionId = c.id)
select json_agg(j.o) AS Result
from basedata,
     lateral jsonb_build_object('activity',
                                jsonb_build_array(
                                        jsonb_build_object('type', 'USER', 'uuid', userId, 'first_name', first_name, 'last_name', last_name),
                                        jsonb_build_object('type', 'COLLECTION', 'uuid', collectionId, 'title', title)
                                    )) j(o);

DBFiddle demo

赞(0）回复(0）举报 2023-06-05

我来回答

postgresql JOIN其他表与jsonb数据类型在postgres内的对象本身

1条答案

相关问题

热门标签

最新问答