我有兩個表，users和simulations。用戶表有一個帶有自動遞增ID欄位的用戶詳情串列。simulations表包含模擬的詳細資訊以及相關用戶的串列。Simulations表中的user_list列是一個jsonb欄位，包含相關用戶的ID串列。
表名：user
資料：
ID name country age
---- ------ --------- -----
1 Jason USA 21
2 William England 40
3 Jake 美國 34
.
.
.
(總共100,000 rows)
表名: simulations
資料：
ID simulation_name user_list datetime
---- --------------- ---------- ----------
1 samples1 [1,2] < some datetime>
2 sample2 [5,6] < some datetime>
.
.
我能夠使用以下查詢來獲取關于特定國家的模擬資料
SELECT * from simulations
WHERE ARRAY（SELECT JSONB_ARRAY_ELEMENTS(user_list)::bigint)
&& ARRAY(SELECT id FROM users
WHERE country = 'USA'）。)
這就得到了以下的回應
ID simulation_name datetime user_list
---- --------------- ---------- ----------
1 sample1 <some datetime> [1,2]
2 sample4 <some datetime> [1,9, 12]
.
.
.
然而，這并不完全是我所需要的。我需要 users_list 列不僅要回傳 ID，還要回傳一個包含每個用戶詳細資訊的 json 物件。
這就是我實際需要的那種回應：
ID simulation_name datetime user_list
------------------- -------------- ------------
1 sample1 <some datetime> [{"id": 1, "name": "Jason", "country": "USA"},{"id":2, "name": "William", "country": "England"}]
.
.
我如何將用戶ID替換為JSON物件串列中的用戶的實際細節，從而獲得上述回應？
（我知道，如果我遵循多對多的表結構，這將會更容易，但由于某些業務需求，我必須遵循這一點）
如果我遵循多對多的表結構，這將更容易。
uj5u.com熱心網友回復：
你可以使用連接或相關的子查詢來檢索相關的用戶詳細資訊，同時使用json_agg來將相關的用戶詳細資訊聚合到一個json陣列中，如下所示。
你也可以使用s.user_list @> u.id::text::jsonb來確定一個用戶是否存在于串列中。
使用 join
SELECT
s.id。
s.sumulation_name,
s.datetime ,
json_agg(u.*) as user_list
FROM[/span
仿真度s
INNER JOIN
u ON s.user_list @> u.id::text::jsonb AND
u.country='USA'
GROUPBY
s.id,s.simulation_name,s.datetime。
| id | simulation_name | 日期用戶串列|
|---|---|---|
| 1 | sample1 | sample1 |
使用相關的查詢
SELECT * FROM (
SELECT
s.id,
s.sumulation_name,
s.datetime ,
(
SELECT json_agg(u.*)
FROM users u
WHERE s.user_list @>/span> u.id::text::jsonb AND
u.country='USA'。
) as user_list
FROM[/span
simulations s
) t
WHERE t.user_list IS NOT NULL;
| id | simulation_name | 日期用戶串列|
|---|---|---|
| 1 | sample1 | sample1 |
查看DB Fiddle上的作業演示
如果這兩種方法對你有用，請告訴我。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/324747.html
標籤：
下一篇：Bash函式引數來自變數