如何在Postgres中执行SELECT语句时从JSONB字段获取JSON对象列表而不是id列表? &

Question

我有两个表，users和．表有一个带有自动递增ID字段的用户详细信息列表。表中包含模拟的详细信息以及相关用户的列表。模拟表中的user_list列是jsonb字段，包含关联用户的列表id。

两个表格的样本如下:

表名:users

数据:

ID       name          country       age
----      ------       ---------     -----
1       Jason          USA          21
2       William        England      40
3       Jake           USA          34
.
.
. 
(total 100,000 rows) 

表名:simulations

数据:

ID   simulation_name     user_list       datetime
----  ---------------    ----------      ----------
1      sample1           [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对象列表，从而获得上述响应?

(我知道如果我遵循多对多表结构，这将会容易得多，但由于某些业务需求，我不得不遵循这一点)

Answer 1

您可以使用连接或相关子查询来检索相关的用户详细信息以及json_agg，以便在json数组中聚合相关的用户详细信息，如下所示。

您也可以使用s.user_list @> u.id::text::jsonb来确定用户是否存在于列表中。

使用加入

SELECT
s.id,
s.simulation_name,
s.datetime ,
json_agg(u.*) as user_list
FROM
simulations s
INNER JOIN
users u ON s.user_list @> u.id::text::jsonb AND
u.country='USA' 
GROUP BY
s.id,s.simulation_name,s.datetime;

<表类>idsimulation_namedatetimeuser_listtbody><<tr>1sample1一些datetime[{"id" 1、"name":"Jason","country":"USA","age": 21}]