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.

Answer 2

You can use the ROWS FROM syntax to combine json_to_recordset and jsonb_to_recordset with WITH ORDINALITY.

The documentation as back as 9.5 says

ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

So this works (tested on 12 but should work at least on all versions >=9.5)

WITH my_json AS 
(
  SELECT '[{"id":1, "name":"somename", "bool":true},
           {"id":2, "name":null, "bool":false}]'::json jsn
)
SELECT jsn_with_ordinality.*
  FROM my_json,
  ROWS FROM (json_to_recordset(jsn) AS (id int, name TEXT, bool boolean)) 
  WITH ORDINALITY jsn_with_ordinality;

Results in:

id	name	bool	ordinality
1	somename	true	1
2		false	2

I do wonder however if it's any safer than just adding a row_number() over()...