Добавьте ORDINALITY в расширенный массив JSON в Postgres 11.7.

Я беру два массива JSONB, распаковываю их и прочесываю результаты. Я пытаюсь добавить WITH ORDINALITY к распаковке массива JSON. Я не смог понять, как добавить WITH ORDINALITY. По какой-то причине я не могу найти WITH ORDINALITY в документации для инструментов JSON Postgres 11:

https://www.postgresql.org/docs/11/functions-json.html

Я видел примеры использования jsonb_array_elements....WITH ORDINALITY, но не смог заставить его работать. Во-первых, функциональный пример на основе массивов Postgres:

WITH
first AS (
SELECT * FROM
    UNNEST (ARRAY['Charles','Jane','George','Percy']) WITH ORDINALITY AS x(name_, index)
),

last AS (
SELECT * FROM
    UNNEST (ARRAY['Dickens','Austen','Eliot']) WITH ORDINALITY AS y(name_, index)
)

SELECT first.name_ AS first_name,
       last.name_  AS last_name

  FROM first
  JOIN last ON (last.index = first.index)

Это дает желаемый результат:

first_name  last_name
Charles     Dickens
Jane        Austen
George      Eliot

Я использую индекс ORDINALITY для создания JOIN, так как я объединяю два списка для попарного сравнения. Я могу предположить, что мои списки одинакового размера.

Однако мой ввод будет массивом JSON, а не массивом Postgres. У меня работает распаковка с jsonb_to_recordset, но не работает генерация порядкового номера. Вот пример, который правильно выполняет распаковку:

DROP FUNCTION IF EXISTS tools.try_ordinality (jsonb, jsonb);
CREATE OR REPLACE FUNCTION tools.try_ordinality (
     base_jsonb_in         jsonb,
     comparison_jsonb_in   jsonb)

RETURNS TABLE (
    base_text         citext,
    base_id           citext,
    comparison_text   citext,
    comparison_id     citext)

AS $BODY$

BEGIN

RETURN QUERY

WITH
base_expanded AS (

 select *
   from jsonb_to_recordset (
          base_jsonb_in) 
      AS base_unpacked (text citext, id citext)
 ),

comparison_expanded AS (

 select *
   from jsonb_to_recordset (
          comparison_jsonb_in)
      AS comparison_unpacked (text citext, id citext)
 ),

combined_lists AS (
select base_expanded.text       AS base_text,
       base_expanded.id         AS base_id,
       comparison_expanded.text AS comparison_text,
       comparison_expanded.id   AS comparison_id

  from base_expanded,
       comparison_expanded
)

select * 

   from combined_lists;

END
$BODY$
LANGUAGE plpgsql;

select *  from try_ordinality (
'[
    {"text":"Fuzzy Green Bunny","id":"1"},
    {"text":"Small Gray Turtle","id":"2"}
    ]',

'[
    {"text":"Red Large Special","id":"3"},
    {"text":"Blue Small","id":"4"},
    {"text":"Green Medium Special","id":"5"}
  ]'
);

Но это CROSS JOIN

base_text    base_id    comparison_text    comparison_id
Fuzzy Green Bunny    1    Red Large Special      3
Fuzzy Green Bunny    1    Blue Small             4
Fuzzy Green Bunny    1    Green Medium Special   5
Small Gray Turtle    2    Red Large Special      3
Small Gray Turtle    2    Blue Small             4
Small Gray Turtle    2    Green Medium Special   5

Я получаю парный результат только с двумя строками:

Fuzzy Green Bunny    1    Red Large Special    3
Small Gray Turtle    2    Blue Small           4

Я попытался переключиться на jsonb_array_elements, как в этом фрагменте:

WITH
base_expanded AS (

 select *
   from jsonb_array_elements (
          base_jsonb_in) 
      AS base_unpacked (text citext, id citext)
 ),

Я вернусь

ERROR:  a column definition list is only allowed for functions returning "record"

Есть ли простой способ получить порядковый номер в распакованном массиве JSON? Это очень просто с UNNEST в массиве Postgres.

  • Я рад узнать, что испортил синтаксис.

  • Я могу CREATE TYPE, если это чем-то поможет.

  • Я могу преобразовать в массив Postgres, если это просто сделать.

Спасибо за любые предложения.


postgresql unnest jsonb
person Morris de Oryx    schedule 10.05.2020    source источник
comment
WITH ORDINALITY применяется к любым функциям, возвращающим множество. У JSON есть функции, возвращающие наборы, с которыми вы можете его использовать, но было бы странно документировать их там, поскольку они работают там так же, как и для функций, не возвращающих наборы JSON.   -  person jjanes    schedule 10.05.2020

Ответы (1)


arrow_upward
1
arrow_downward

Вы делаете это точно так же.

with first as (
  select *
  from jsonb_array_elements('[
    {"text":"Fuzzy Green Bunny","id":"1"},
    {"text":"Small Gray Turtle","id":"2"}
    ]'::jsonb) with ordinality as f(element, idx)
), last as (
  select *
  from jsonb_array_elements('[
    {"text":"Red Large Special","id":"3"},
    {"text":"Blue Small","id":"4"},
    {"text":"Green Medium Special","id":"5"}
  ]'::jsonb) with ordinality as f(element, idx)
)
SELECT first.element ->> 'text' AS first_name,
       last.element ->> 'text' AS last_name
FROM first
  JOIN last ON last.idx = first.idx
person a_horse_with_no_name    schedule 10.05.2020