Looping on jsonb - PostgreSQL

Question

I've created a sample table to explain the problem I'm facing easily. The column transferList will be of length >= 2. When it's of length = 2, my code works. When it's > 2, I need to consider the first and second elements in jsonb first, and then the second and third elements, similar to looping. I have trouble doing this as it's not possible to use for loops within select statements. I'm guessing I should be using lateral/cross join and unnest, but I'm not comfortable with those concepts.

CREATE TABLE temp.transfers (id varchar(20), transferList jsonb);

INSERT INTO temp.transfers(id, transferList)
values ('id1', '[
    {
        "team": "team1",
        "queue": "queue1",
        "initiator": "initiator1"
    },
    {
        "team": "team2",
        "queue": "queue2",
        "initiator": "initiator2"
    },
    {
        "team": "team3",
        "queue": "queue3",
        "initiator": "initiator3"
    }
]'::jsonb);

INSERT INTO temp.transfers(id, transferList)
values ('id2', 
'[
    {
        "team": "team10",
        "queue": "queue10",
        "initiator": "initiator1"
    },
    {
        "team": "team20",
        "queue": "queue20",
        "initiator": "initiator2"
    }
]'::jsonb);

This is my current code. It only works when length(transferList)=2. When this query is run on the above table, the 2nd row in the desired output doesn't get displayed.

SELECT 
id,
(transferList->>0)::jsonb ->>'team' AS start_team,
(transferList->>0)::jsonb ->> 'queue' AS start_queue,
(transferList->>1)::jsonb ->>'team' AS end_team,
(transferList->>1)::jsonb ->> 'queue' AS end_queue
FROM
temp.transfers;

This is the desired output.

id   |  start_team  |  start_queue  |  end_team  |  end_queue  |
id1  |  team1       |  queue1       |  team2     |  queue2     |
id1  |  team2       |  queue2       |  team3     |  queue3     |
id2  |  team10      |  queue10      |  team20    |  queue20    |

Any help would be appreciated, thanks.

user_5070879 · Answer

Using json_array_elements and self-join:

WITH cte AS (
  SELECT t.id,
       s.p,
       s.v->>'team' AS team,
       s.v->>'queue' AS queue,
       s.v->>'initiator' AS initiatior
  FROM transfers t
  ,LATERAL json_array_elements(t.transferList::JSON) WITH ORDINALITY AS s (v, p)
)
SELECT c.id, 
       c.team AS start_team,
       c.queue AS start_queue,  
       c2.team AS end_team,  
       c2.queue AS end_queue
FROM cte c
JOIN cte c2
  ON c.id = c2.id
 AND c.p = c2.p-1
ORDER BY c.id, c.p;

db<>fiddle demo

Output:

user_2445629 · Answer

Window functions (LEAD / LAG) can be used to pair the adjacent rows nicely too (leveraging lukasz approach). Please leave the checkmark, as this is just a possible alternate ending to the story:

WITH cte AS (
      SELECT t.id
           , s.p
           , s.v->>'team'      AS start_team
           , s.v->>'queue'     AS start_queue
           , s.v->>'initiator' AS initiatior
           , LEAD(s.v->>'team')  OVER w AS end_team
           , LEAD(s.v->>'queue') OVER w AS end_queue
       FROM transfers t
          , LATERAL json_array_elements(t.transferList::JSON) WITH ORDINALITY AS s (v, p)
     WINDOW w AS (PARTITION BY id ORDER BY p)
     )
SELECT id, start_team, start_queue, end_team, end_queue
  FROM cte
 WHERE end_team IS NOT NULL
 ORDER BY id, p
;

Looping on jsonb - PostgreSQL

2 Answers