在Postgres中，使用jsonb数据类型内部的对象连接其他表。

huangapple
go评论85阅读模式
英文:

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

问题

  1. 表格模式，我想要连接的表格如下：
  1. uuid uuid NOT NULL DEFAULT gen_random_uuid(),
  2. actor_uuid uuid NOT NULL,
  3. ref_uuid uuid NOT NULL,
  4. ref_type character varying(255) COLLATE pg_catalog."default" NOT NULL,
  5. created_at timestamp with time zone NOT NULL,
  6. activity jsonb NOT NULL,
  7. CONSTRAINT activity_log_pkey PRIMARY KEY (uuid)
  2. 在activity列中的数据将会是类似以下的格式：

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

  2. 我想要在类型为USER的特定对象中连接我的用户表格，类似地，对于COLLECTION也是如此。
  4. 请问我如何连接这两个表格并在响应中获得类似以下的结果：
  6. ```plaintext
  7. [
  8.   {
  9.     activity: [
  10.        {
  11.         type: "USER",
  12.         uuid: "6ae0fa04-804d-492a-94fa-ccf75556379f",  
  13.         first_name: "Henil",
  14.         last_name: "Mehta"
  15.        },
  16.        {
  17.         type: "MESSAGE",
  18.         
  19.         name: "Netflix"
  20.        },
  21.        {
  22.         type: "COLLECTION",
  23.         uuid: "7qe0fa04-804d-492a-94fa-ccf75556379f",   
  24.         title: "Netflix"
  25.        },
  26.     ] 
  27.   }
  28. ]
英文:

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

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

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

  1. [{"type": "USER", 
  2. "uuid": "6ae0fa04-804d-492a-94fa-ccf75556379f"}, 
  3. {"type": "MESSAGE", 
  4. "message": "activity_template.collection_details.updated"}, 
  5. {"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

  1. [
  2.   {
  3.     activity: [
  4.        {
  5.         type: "USER",
  6.         uuid: "6ae0fa04-804d-492a-94fa-ccf75556379f",  
  7.         first_name: "Henil",
  8.         last_name: "Mehta"
  9.        },
  10.        {
  11.         type: "MESSAGE",
  12.         
  13.         name: "Netflix"
  14.        },
  15.        {
  16.         type: "COLLECTION",
  17.        uuid: "7qe0fa04-804d-492a-94fa-ccf75556379f",   
  18.         title: "Netflix"
  19.        },
  20.     ] 
  21.   }
  22. ]
  24. </details>
  27. # 答案1
  28. **得分**: 1
  30. 以下是翻译好的部分：
  32. ```sql
  33. with user_col (id, userId, collectionId) as (select id
  34.                                                   , cast(
  35.                 jsonb_path_query_first(activity, '$[*] ? (@.type == "USER")') #>> '{uuid}' as uuid)       as userId
  36.                                                   , cast(
  37.                 jsonb_path_query_first(activity, '$[*] ? (@.type == "COLLECTION")') #>> '{uuid}' as uuid)       as collectionId
  38.                                              from t1),
  39.      basedata as
  40.          (select userId,
  41.                  collectionId,
  42.                  first_name,
  43.                  last_name,
  44.                  title
  45.           from user_col uc
  46.                    left join users u on uc.userId = u.id
  47.                    left join collections c on uc.collectionId = c.id)
  48. select json_agg(j.o) AS Result
  49. from basedata,
  50.      lateral jsonb_build_object('activity',
  51.                                 jsonb_build_array(
  52.                                         jsonb_build_object('type', 'USER', 'uuid', userId, 'first_name', first_name, 'last_name', last_name),
  53.                                         jsonb_build_object('type', 'COLLECTION', 'uuid', collectionId, 'title', title)
  54.                                     )) j(o);

DBFiddle demo

英文: 
  1. with user_col (id, userId, collectionId) as (select id
  2.                                                   , cast(
  3.                 jsonb_path_query_first(activity, &#39;$[*] ? (@.type == &quot;USER&quot;)&#39;) #&gt;&gt; &#39;{uuid}&#39; as uuid)       as userId
  4.                                                   , cast(
  5.                 jsonb_path_query_first(activity, &#39;$[*] ? (@.type == &quot;COLLECTION&quot;)&#39;) #&gt;&gt;
  6.                 &#39;{uuid}&#39; as uuid)                                                                         as collectionId
  7.                                              from t1),
  8.      basedata as
  9.          (select userId,
  10.                  collectionId,
  11.                  first_name,
  12.                  last_name,
  13.                  title
  14.           from user_col uc
  15.                    left join users u on uc.userId = u.id
  16.                    left join collections c on uc.collectionId = c.id)
  17. select json_agg(j.o) AS Result
  18. from basedata,
  19.      lateral jsonb_build_object(&#39;activity&#39;,
  20.                                 jsonb_build_array(
  21.                                         jsonb_build_object(&#39;type&#39;, &#39;USER&#39;, &#39;uuid&#39;, userId, &#39;first_name&#39;, first_name, &#39;last_name&#39;, last_name),
  22.                                         jsonb_build_object(&#39;type&#39;, &#39;COLLECTION&#39;, &#39;uuid&#39;, collectionId, &#39;title&#39;, title)
  23.                                     )) j(o);

DBFiddle demo

huangapple
  • 本文由 发表于 2023年6月1日 19:32:36
  • 转载请务必保留本文链接：https://go.coder-hub.com/76381424.html
  • join
  • jsonb
  • knex.js
  • postgresql
  • select
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定