Postgres - two selects to two json arrays

Question

How can i make two SELECT's into two json arrays from the same table?

╔════╦══════════════╦
║ id ║  users       ║ 
╠════╬══════════════╬
║  1 ║ Jeff Atwood  ║
║  2 ║ Geoff Dalgas ║
║  3 ║ Jarrod Dixon ║
║  4 ║ Joel Spolsky ║ 
╚════╩══════════════╩

How to combine this two selects in one query?

First select example

SELECT JSON_AGG(arr1) as arr1
FROM (
  SELECT *
  FROM users
  WHERE id>1
) arr1

Second select example

SELECT JSON_AGG(arr2) as arr2
FROM (
  SELECT *
  FROM users
  WHERE id<2
) arr2

Exprected:

[arr1: [{id:1}, {id:2}], arr2: [{id:1}, {id:2}]]

The expected output is not valid JSON array syntax. What do you really want, an object? — Bergi
– Bergi, Commented Jul 22, 2020 at 13:54
@a_horse_with_no_name no, each of 2 selects should have different where — ZiiMakc
– ZiiMakc, Commented Jul 22, 2020 at 13:55
So, just SELECT json_agg(*) AS arr1, json_agg(*) AS arr2 FROM users? — Bergi
– Bergi, Commented Jul 22, 2020 at 13:56

score 1 · Accepted Answer · 2020-07-22 14:13:40Z

1

You can use a conditional aggregation for that:

SELECT JSON_AGG(u) filter (where id > 1) as arr1,
       JSON_AGG(u) filter (where id < 2) as arr2
FROM users u

^{(online demo)}

edited Jul 22, 2020 at 14:13

answered Jul 22, 2020 at 14:02

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - two selects to two json arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related