Postgresql 9.6: Query specific ranges of a JSONB array

Question

I use Postgresql 9.6 currently and been trying since hours to get the following solved in the most efficient way:

My example table:

id | data
-----------------------------------------------------------------------------------------------------------
10 | {"scores": [{"u": "Peter", s: 120}, {"u": "Joe", s: 100}, {"u": "Pam", s: 70}, {"u": "Lisa", s: 120}]}
11 | {"scores": [{"u": "Mae", s: 320}, {"u": "Max", s: 230}, {"u": "Moe", s: 170}, {"u": "Mini", s: 120}]}
12 | {"scores": [{"u": "Jack", s: 140}, {"u": "John", s: 110}, {"u": "Wes", s: 70}, {"u": "Mick", s: 20}]}

I need a query to get the top 2 Usernames (u) of each row.

So my result should look like this:

users
-----
Peter
Joe
Mae
Max
Jack
John

And a query to get the 2nd and 3rd usernames:

users
-----
Joe
Pam
Max
Moe
John
Wes

What would be the most efficient way to solve this? My real tables have arrays with 100-400 elements and about 1500 rows.

If you need to do this often, then maybe think about the advice given in the manual regarding normal arrays: "Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements". This is also true for arrays hidden inside JSON object — user330315
– user330315, Commented Oct 27, 2017 at 14:56

Tomer Sela · Accepted Answer · 2017-10-29 01:57:10Z

4

First 2 usernames:

SELECT scores -> 'u'
FROM (
  SELECT  ROW_NUMBER() OVER(PARTITION BY id) rn, scores
  FROM (
    SELECT id, data, jsonb_array_elements(data -> 'scores') AS scores
    FROM scores
  ) s1
) s2
WHERE rn <= 2;

2nd and 3rd usernames:

SELECT scores -> 'u'
FROM (
  SELECT  ROW_NUMBER() OVER(PARTITION BY id) rn, scores
  FROM (
    SELECT id, data, jsonb_array_elements(data -> 'scores') AS scores
    FROM scores
  ) s1
) s2
WHERE rn = 2 OR rn = 3;

Another option would be:

SELECT username
FROM (
  SELECT unnest(ARRAY [(data #> '{scores,0,u}') :: TEXT, (data #> '{scores,1,u}') :: TEXT]) username
  FROM scores
) usernames WHERE username NOTNULL;

2nd and 3rd usernames:

SELECT username
FROM (
  SELECT unnest(ARRAY [(data #> '{scores,1,u}') :: TEXT, (data #> '{scores,2,u}') :: TEXT]) username
  FROM scores
) usernames WHERE username NOTNULL;

edited Oct 29, 2017 at 1:57

answered Oct 29, 2017 at 1:45

Tomer Sela

49111 silver badges18 bronze badges

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

1 Comment

Smrchy Over a year ago

Thanks @Tomer this was exactly what i was missing.

Collectives™ on Stack Overflow

Postgresql 9.6: Query specific ranges of a JSONB array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related