Получение row_number для из jsonb_array_elements или jsonb_to_recordset

Я делаю поперечное перекрестное соединение в массиве jsonb, и я хочу получить row_number (или его эквивалент) для элементов массива. глядя на документы row_number, я вижу, что мне нужно было бы выполнить «порядок по» в дополнение к «разделу по», но на самом деле нет критерия сортировки, который я мог бы использовать - массивы просто имеют установленный порядок, и мне нужно чтобы получить индекс в массив вместе с остальными данными.

В клиентской таблице будут такие записи

{
  "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"
    }
  ]
}

и я бы запустил запрос вроде

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

но мне нужно каким-то образом получить waist.position_in_array.


postgresql json jsonb
person retorquere    schedule 18.10.2017    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Используйте функцию jsonb_array_elements(...) _ 2_

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.

person klin    schedule 18.10.2017

arrow_upward
0
arrow_downward

Вы можете использовать синтаксис ROWS FROM для объединения json_to_recordset и jsonb_to_recordset с WITH ORDINALITY.

В документации, начиная с 9.5, говорится

СТРОКИ ОТ (вызов_функции [, ...]) [С ПОРЯДОК] [[AS] псевдоним_таблицы [(псевдоним_столбца [, ...])]]

Так что это работает (проверено на 12, но должно работать как минимум на всех версиях ›= 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;

Результаты в:

id|name    |bool |ordinality|
--|--------|-----|----------|
 1|somename|true |         1|
 2|        |false|         2|

Однако мне интересно, безопаснее ли это, чем просто добавить row_number() over()...

person Pyrocks    schedule 02.12.2020