Convert jsonb column to a user-defined type

Question

I'm trying to convert each row in a jsonb column to a type that I've defined, and I can't quite seem to get there.

I have an app that scrapes articles from The Guardian Open Platform and dumps the responses (as jsonb) in an ingestion table, into a column called 'body'. Other columns are a sequential ID, and a timestamp extracted from the response payload that helps my app only scrape new data.

I'd like to move the response dump data into a properly-defined table, and as I know the schema of the response, I've defined a type (my_type).

I've been referring to the 9.16. JSON Functions and Operators in the Postgres docs. I can get a single record as my type:

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

produces

id	type	sectionId	...
example_id	example_type	example_section_id	...

(abbreviated for concision)

If I remove the limit, I get an error, which makes sense: the subquery would be providing multiple rows to jsonb_populate_record which only expects one.

I can get it to do multiple rows, but the result isn't broken into columns:

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

produces:

jsonb_populate_record
(example_id_1,example_type_1,example_section_id_1,...)
(example_id_2,example_type_2,example_section_id_2,...)
(example_id_3,example_type_3,example_section_id_3,...)

This is a bit odd, I would have expected to see column names; this after all is the point of providing the type.

I'm aware I can do this by using Postgres JSON querying functionality, e.g.

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

This works but it seems quite inelegant. Plus I lose datatypes.

I've also considered aggregating all rows in the body column into a JSON array, so as to be able to supply this to jsonb_populate_recordset but this seems a bit of a silly approach, and unlikely to be performant.

Is there a way to achieve what I want, using Postgres functions?

Stefanov.sm · Accepted Answer · 2021-11-01T14:04:53.807

Maybe you need this - to break my_type record into columns:

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

i.e. select all from these my_type records. All column names and types are in place.
Here is an illustration. My custom type is delmet and CTO t remotely mimics 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;

Result:

i	x	y	z	k
1	10	Nope	true	cats
2	11	Yep	false	dogs
3	12		true	parrots

Convert jsonb column to a user-defined type

1 Answers1