Блог

У меня есть две таблицы, пользователи и симуляторы. Таблица пользователи содержит список сведений о пользователе с полем идентификатора автоматического увеличения. Таблица моделирования содержит подробные сведения о моделировании, а также список связанных пользователей. user_list Столбец в таблице моделирования представляет собой jsonb поле, содержащее список идентификаторов связанных пользователей.

Примеры двух таблиц приведены ниже:

Имя таблицы: пользователи

Данные:

   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)
      amp;amp; 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 , чтобы столбец возвращал не только идентификаторы, но и объект json, содержащий сведения о каждом пользователе.
Это тот ответ, который мне действительно нужен:

  ID   simulation_name      datetime                                user_list
---  ----------------   --------------                            ------------
 1      sample1         <some datetime>     [{"id":1,"name":"Jason","country":"USA"},{"id":2,"name":"William","country":"England"}]
 .
 .
 

Как заменить идентификаторы пользователей фактическими данными пользователей в виде списка объектов JSON, чтобы получить вышеуказанный ответ?

(Я знаю, что это было бы намного проще, если бы я следовал структуре таблиц «многие ко многим», но из-за определенных бизнес-требований я должен следовать этому)

Вы можете использовать объединенный или коррелированный подзапрос для получения связанных сведений о пользователе, а также 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;
 

Использование коррелированного запроса

 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;
 

Просмотр рабочей демо-версии на DB Fiddle

Дайте мне знать, если что-то из этого вам подойдет.