JOIN other tables with jsonb data type in postgres inside that object itself

The table schema for which I want to join the tables are :-

    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)

in the activity column the data will be some what like :-

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

I want to join my user table in the particular object where the type is USER and like wise for the COLLECTION

PLease how can I join the table and get the response somewhere like

[
  {
    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"
       },
    ] 
  }
]

</details>


# 答案1
**得分**: 1

以下是翻译好的部分：

```sql
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

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);