Combine multiple sql queries into a single sql query in postgresql

user_18280526 0
Viewed 263

I have 3 different PostgreSQL queries, getUsers, getCompanies, getEvents for my homepage. I want to create a single query for this.

My 3 simplified queries:

getUsers = SELECT * FROM users;
getCompanies = SELECT * FROM companies;
getEvents = SELECT * FROM events;

I want to get an array of objects with this query. What I tried so far:

SELECT
  coalesce(json_agg(users), '[]'::json) as users,
  coalesce(json_agg(companies), '[]'::json) as companies,
  coalesce(json_agg(events), '[]'::json) as events
    FROM
    (
     SELECT
       *
     FROM
       users
    ) AS users,
    (
      SELECT
       *
     FROM
       companies
    ) AS companies,
    (
    SELECT
      *
    FROM
      events
     ) AS events;

For a single query this works fine:

SELECT
  coalesce(json_agg(companies), '[]'::json) as companies
FROM
  (SELECT
    *
   FROM
    companies
    ) as companies

Output is for this single query:

companies [
  {
    name: 'test company',
    url: 'https://www.example.com/test-company',
  },
  {
    name: 'test agency',
    url: 'https://www.example.com/test-agency',
  }
]

RESULT should be:

users: [{...},{...}], 
companies: [{...},{...}], 
events: [{...},{...}]
postgresql sql
2 Answers

Try this:

SELECT
  coalesce(json_agg(companies), '[]'::json) as companies
FROM
  (SELECT
    *
   FROM
    companies
    ) as companies

UNION
SELECT 
coalesce(json_agg(users), '[]'::json) as users 
FROM 
   (
     SELECT
       *
     FROM
       users
    ) AS users
UNION
SELECT 
coalesce(json_agg(events), '[]'::json) as events 
FROM 
   (
    SELECT
      *
    FROM
      events
     ) AS eventsl
user_62856000

My solution after a little bit of struggle. Thanks to nikhil sugandh for putting me on the right path!

SELECT
 (jsonb_agg(companies)) as result

FROM
  (SELECT
    *
   FROM
    companies
    ) as companies

UNION
SELECT 
(jsonb_agg(users)) as users  
FROM 
   (
     SELECT
       *
     FROM
       users
    ) AS users
UNION
SELECT 
(jsonb_agg(events)) as events
FROM 
   (
    SELECT
      *
    FROM
      events
     ) AS events

Here the link to the DB Fiddle

user_182805260
Related