时间戳

首页 > 解决方案 > 在 Postgres 中执行 SELECT 语句时，如何从 JSONB 字段中获取 JSON 对象列表而不是 ID 列表？

sql - 在 Postgres 中执行 SELECT 语句时，如何从 JSONB 字段中获取 JSON 对象列表而不是 ID 列表？

问题描述

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

下面给出了这两个表的示例：

表名：用户

数据：

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

表名：模拟

数据：

 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 对象列表以获得上述响应？

（我知道如果我遵循多对多表结构，这会容易得多，但由于某些业务需求，我必须遵循这一点）

标签： sqlpostgresqljsonb

解决方案

您可以使用连接或相关子查询来检索相关用户详细信息，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;
ID 模拟名称 约会时间 用户列表
1 样品1 一些日期时间 [{"id":1,"name":"Jason","country":"USA","age":21}]

使用相关查询

SELECT * FROM (
SELECT
    s.id,
    s.simulation_name,
    s.datetime ,
    (
        SELECT json_agg(u.*)
        FROM users u
        WHERE s.user_list @> u.id::text::jsonb AND
              u.country='USA' 
    ) as user_list
FROM
    simulations s
) t
WHERE t.user_list IS NOT NULL;
ID 模拟名称 约会时间 用户列表
1 样品1 一些日期时间 [{"id":1,"name":"Jason","country":"USA","age":21}]

在 DB Fiddle 上查看工作演示

让我知道是否适合您。

推荐阅读