How to fetch a list of JSON objects instead of list of IDs from a JSONB field when doing a SELECT statement in Postgres?

user_2315762 0
Viewed 615

I have two tables, users and simulations. The users table has a list of user details with an auto-increment ID field. The simulations table contains details of simulations along with a list of associated users. The user_list column in the simulations table is a jsonb field containing a list IDs of the associated users.

Samples of the two tables are given below:

Table Name: users

Data:

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

Table Name: simulations

Data:

 ID   simulation_name     user_list       datetime
----  ---------------    ----------      ----------
 1      sample1           [1,2]         <some datetime>
 2      sample2           [5,6]         <some datetime>
 .
 .
 .

I am able to fetch data regarding simulations of a specific country using the following query

SELECT * from simulations
WHERE ARRAY(SELECT JSONB_ARRAY_ELEMENTS(user_list)::bigint)
      && ARRAY(SELECT id FROM users
               WHERE country = 'USA');

This gives the following response

 ID   simulation_name       datetime            user_list      
----  ---------------       ----------         ----------
 1      sample1           <some datetime>        [1,2]         
 2      sample4           <some datetime>       [1,9,12]      
 .
 .
 .

However, this is not exactly what I need. I need the users_list column to return not just IDs but a json object containing details of each user as well. This is the kind of response I actually need:

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

How do I replace user IDs with the actual details of the users as a list of JSON objects so as to get the above response?

(I know that this would have been much easier if I followed a many-to-many table structure but due to certain business requirements I have to follow this)

jsonb postgresql sql
1 Answers

You could use a join or correlated subquery to retrieve the related user details along with json_agg to aggregate the related user details in a json array as shown below.

You may also use s.user_list @> u.id::text::jsonb to determine whether a user exists in the list.

Using join

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 simulation_name datetime user_list
1 sample1 some datetime [{"id":1,"name":"Jason","country":"USA","age":21}]

Using correlated query

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 simulation_name datetime user_list
1 sample1 some datetime [{"id":1,"name":"Jason","country":"USA","age":21}]

View working demo on DB Fiddle

Let me know if either works for you.

user_43071360
Related