How to SELECT rows in an order dictated by a JSONB array of primary key IDs?

Question

This is my dummy setup:

CREATE TABLE containers (
    id SERIAL PRIMARY KEY,
    positions jsonb
);
CREATE TABLE bits (
    id SERIAL PRIMARY KEY,
    container_id integer REFERENCES containers(id) ON DELETE CASCADE ON UPDATE CASCADE,
    data jsonb
);

Example row in containers:

id  positions
1   [4, 2, 3]

What I'm trying to accomplish is to use the positions in containers to dictate the order in which bits are returned. This seems like it might be easier than using a smallint position column in bits that has values like 0, 1, 2, 3, and must all be updated when the user reorders the bits.

In essence, what I'm trying to do is use the positions array in ORDER BY, e.g. (pseudo-code):

SELECT b.id, b.data FROM bits b, container c WHERE b.container_id = 1 ORDER BY (jsonb_array_elements(c.positions));

The desired output would be:

id  data
4   {"banner": "This is a message!"}
2   {"name": "Bob"}
3   {"playlistId": 3}

How do I accomplish this? I'm using Postgres 10.7.

score 2 · Accepted Answer · edited Sep 23 '20 at 17:54

2

You need to use jsonb functions to do this.

Please try something like this:

select b.*
  from bits b
       join containers c
         on c.id = b.container_id
       join lateral jsonb_array_elements_text(c.positions) 
                      with ordinality as p(id, rn)
         on p.id::int = b.id
 where b.container_id = 1
 order by p.rn::int

edited Sep 23 '20 at 17:54

ffxsam

26,428
32
94
144

answered Sep 23 '20 at 17:22

Mike Organek

11,647
3
11
26

This returns the bits in the wrong order (2, 3, 4 - instead of the expected 4, 2, 3). – ffxsam Sep 23 '20 at 17:31
Ah, just had to change the `ORDER` clause: `ORDER BY p.rn::int` – ffxsam Sep 23 '20 at 17:37
@ffxsam Sorry about that. I was thinking that `container_id` should have been the first ordering term. – Mike Organek Sep 23 '20 at 17:44
Nope, actually the query will use a `WHERE` clause for container id. So this will make a great view where I'll just sort by `p.rn` and then I can pass `WHERE container_id = X` when querying the view. – ffxsam Sep 23 '20 at 17:48
1

Hope you don't mind, I made a minor edit to your answer so it fits the question more closely and it's clearer to others. – ffxsam Sep 23 '20 at 17:54
1

@ffxsam Not at all. I did not know whether you wanted the `container_id` to group the rows in your output. That is what led to my first mistake with `b.id`. Good luck with your project! – Mike Organek Sep 23 '20 at 17:57
1

Thanks, this was a big help! – ffxsam Sep 23 '20 at 18:13
@ffxsam FWIW, the reason there is no easy way to get from a `jsonb` array to a native PostgreSQL array is that json arrays are not typed--they can contain any valid json at each index. – Mike Organek Sep 23 '20 at 18:15

How to SELECT rows in an order dictated by a JSONB array of primary key IDs?

1 Answers1