Getting row_number for from jsonb_array_elements or jsonb_to_recordset

Question

I'm doing a cross join lateral on an jsonb array and I'm looking to get the row_number (or its equivalent) for the array elements. looking at the row_number docs I see that I'd need to do a "order by" in addition to the "partition by", but there's not really a sort criterion I could use -- arrays just have a set order, and I need to retrieve the index into the array along with the rest of the data.

The client table will have entries like this

{
  "id": "cj49q33oa000",
  "email": {
    "address": "",
    "after": "2016-06-28T12:28:58.016Z",
    "error": "Et corporis sed."
  },
  "name": "Arnold Schinner",
  "birthdate": "2016-07-29T05:09:33.693Z",
  "status": "paused",
  "sex": "f",
  "waist": [
    {
      "completed": "2017-06-23T10:37:37.500Z"
    },
    {
      "planned": "2017-06-23T10:37:37.500Z"
    },
    {
      "planned": "2017-06-23T10:37:37.500Z"
    },
    {
      "planned": "2017-06-23T10:37:37.500Z"
    }
  ]
}

and I'd run a query like

SELECT client->>'id' AS id, waist.planned
  FROM clients
 CROSS JOIN LATERAL JSONB_TO_RECORDSET(client->'waist') 
                 AS waist(planned TIMESTAMP WITH TIME ZONE)
 WHERE waist.planned IS NOT NULL

but I need to get waist.position_in_array in some way.

Answer 1

Use the function jsonb_array_elements(...) with ordinality.

select 
    client->>'id' as id, 
    (value->>'planned')::timestamptz as planned, 
    ordinality
from clients
cross join lateral jsonb_array_elements(client->'waist') with ordinality
where value->>'planned' is not null;

      id      |         planned          | ordinality 
--------------+--------------------------+------------
 cj49q33oa000 | 2017-06-23 12:37:37.5+02 |          2
 cj49q33oa000 | 2017-06-23 12:37:37.5+02 |          3
 cj49q33oa000 | 2017-06-23 12:37:37.5+02 |          4
(4 rows)

Db<>fiddle.