Блог

Я пытаюсь преобразовать каждую строку в столбце jsonb в тип, который я определил, и, похоже, у меня не получается.

У меня есть приложение, которое удаляет статьи с открытой платформы Guardian и сбрасывает ответы (как jsonb) в таблицу приема, в столбец с именем ‘body’. Другие столбцы представляют собой последовательный идентификатор и временную метку, извлеченную из полезной нагрузки ответа, которая помогает моему приложению только очищать новые данные.

Я хотел бы переместить данные дампа ответа в правильно определенную таблицу, и, поскольку я знаю схему ответа, я определил тип ( my_type ) .

Я ссылался на функции и операторы 9.16. JSON в документах Postgres. Я могу получить одну запись в качестве своего типа:

select * from jsonb_populate_record(null::my_type, (select body from data_ingestion limit 1));

производит

(сокращенно для краткости)

Если я удалю ограничение, я получу сообщение об ошибке, что имеет смысл: подзапрос будет предоставлять несколько строк, для jsonb_populate_record которых ожидается только одна.

Я могу заставить его выполнять несколько строк, но результат не разбивается на столбцы:

select jsonb_populate_record(null::my_type, body) from reviews_ingestion limit 3;

производит:

Это немного странно, я бы ожидал увидеть имена столбцов; в конце концов, это точка предоставления типа.

Я знаю, что могу сделать это, используя функциональность запросов Postgres JSON, например

 select 
  body -> 'id' as id,
  body -> 'type' as type,
  body -> 'sectionId' as section_id,
  ...
from reviews_ingestion;
 

Это работает, но кажется довольно неэлегантным. Плюс я теряю типы данных.

Я также рассматривал возможность объединения всех строк в body столбце в массив JSON, чтобы иметь возможность предоставить это jsonb_populate_recordset , но это кажется немного глупым подходом и вряд ли будет эффективным.

Есть ли способ добиться того, чего я хочу, используя функции Postgres?

Может быть, вам это нужно — разбить my_type запись на столбцы:

 select (jsonb_populate_record(null::my_type, body)).* 
from reviews_ingestion
limit 3;
-- or whatever other query clauses here
 

т.е. выбрать все из этих my_type записей. Все имена и типы столбцов на месте.
Вот иллюстрация. Мой пользовательский тип — это delmet и CTO t удаленно имитирует data_ingestion .

 create type delmet as (x integer, y text, z boolean);

with t(i, j, k) as 
(
 values 
 (1, '{"x":10, "y":"Nope", "z":true}'::jsonb, 'cats'), 
 (2, '{"x":11, "y":"Yep", "z":false}', 'dogs'), 
 (3, '{"x":12, "y":null, "z":true}', 'parrots')
)
select i, (jsonb_populate_record(null::delmet, j)).*, k
from t;
 

Результат: