How to convert plain values with arrays into a data structure in PostgreSQL?

Question

I have this demo query that starts from a list of "author + articles" values:

VALUES
('author1', ARRAY[('title1', 'content1'), ('title2', 'content2')]),
('author2', ARRAY[('title3', 'content3'), ('title4', 'content4')])

I'd like to convert this into the following data structure:

author	title	content
author1	title1	content1
author1	title2	content2
author2	title3	content3
author2	title4	content4

I've come this far:

WITH "raw_data" ("author", "articles") AS  (
    VALUES
        ('author1', ARRAY[('title1', 'content1'), ('title2', 'content2')]),
        ('author2', ARRAY[('title3', 'content3'), ('title4', 'content4')])
),
"articles" AS (
    SELECT "author", unnest("articles") AS "article" FROM "raw_data"
)
SELECT author, article FROM "articles";

But I get the following:

author	article
author1	(title1, content1)
author1	(title2, content2)
author2	(title3, content3)
author2	(title4, content4)

And I need to find a way to convert the record (title2, content1) into 2 distinct columns.

Thank you all!

ggordon · Accepted Answer · 2021-08-15 12:43:44Z

1

You may achieve this by creating a custom type for your record and casting to it.

See example below

Schema (PostgreSQL v13)

Query #1

create type article as (title text, contents text);

There are no results to be displayed.

Query #2

WITH "raw_data" ("author", "articles") AS  (
    VALUES
        ('author1', ARRAY[('title1', 'content1'), ('title2', 'content2')]),
        ('author2', ARRAY[('title3', 'content3'), ('title4', 'content4')])
),
"articles"  AS (
    SELECT "author", ((unnest("articles"))::text::article).* FROM "raw_data"
)
SELECT * FROM "articles";

author	title	contents
author1	title1	content1
author1	title2	content2
author2	title3	content3
author2	title4	content4

View on DB Fiddle

Just breaking down the expression ((unnest("articles"))::text::article).*

(unnest("articles") - converts your array to rows
((unnest("articles"))::text - casts each record to text (unable to cast directly to article at this time)
(unnest("articles"))::text::article - cast each record to your article type
((unnest("articles"))::text::article).* - expand or select all from your record to get both columns

Let me know if this works for you

answered Aug 15, 2021 at 12:43

ggordon

10.1k2 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marcopeg Over a year ago

Follow up question @ggordon : do you have any idea how to achieve a similar result starting from a single JSON document? (I'm preparing a JSON on PostgreSQL lecture and I'm putting together a few use-cases that are normally overlooked in favor of multiple queries and application logic)

ggordon Over a year ago

You may create another question with some sample data and expected results and share the link here so that I and other S/O users may take a look at it

marcopeg Over a year ago

Btw, this is the resulting tutorial thanks to your help: github.com/marcopeg/amazing-postgresql/tree/main/projects/…

Collectives™ on Stack Overflow

How to convert plain values with arrays into a data structure in PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related