Nested array in a SQL query

Question

I'm fairly new to programming and I'm struggling with an SQL Query.

I want, that the purchased articles (article_id) are grouped in an array, so they are connected to the date column. For a further process, they might be combined ( so date x is combined with articles_id [A,B,C])

To make it clearer I want to show what I've done below..

Starting point is this example table:

Date	Customer_id	article_id
123	1	A
123	1	C
125	1	B
124	2	A
126	2	C

With

SELECT 
customer_id,
array_agg(UNIX_SECONDS(Date)) AS Date,
STRING_AGG(((CAST(article_id AS STRING)))) AS article_id
FROM table
GROUP BY customer_id;

I managed to transform the table to

Customer_id	date	article_id
1	[123, 125]	A, B, C
2	[124, 126]	A, C

No finally the question!

is it possible

To get to :

Customer_id	date	article_id
1	[123, 125]	[[A, C], [B]]
2	[124, 126]	[[A], [C]]

And if so, how?

I'm thankful for every piece of advice or wording to continue my search. And of cause thank you for your time.

jszule · Accepted Answer · 2022-05-01 17:06:21Z

1

what about this? Aggregate twice, first for customer_id, date_time. Then simply for customer_id. (Sorry I've called the date column to date_time)

with input as (
select 123 date_time, 1 customer_id, 'A' article_id
UNION ALL
select 123 date_time, 1 customer_id, 'C' article_id
UNION ALL
select 125 date_time, 1 customer_id, 'B' article_id
UNION ALL
select 124 date_time, 2 customer_id, 'A' article_id
UNION ALL
select 126 date_time, 2 customer_id, 'C' article_id)


select customer_id, array_agg(distinct date_time) date_time, array_agg(STRUCT(article_id)) article_id from (
   select customer_id,date_time, array_agg(article_id) article_id from input
   group by customer_id, date_time
) group by customer_id

edited May 1, 2022 at 17:06

answered May 1, 2022 at 16:20

jszule

4322 silver badges6 bronze badges

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

1 Comment

Martin Over a year ago

Thank you so much, I think this is what I wanted, finally :) date_time isn't an issue ;) Call it what you want.

Bruce · Accepted Answer · 2022-05-01 16:02:13Z

0

Try doing it in 2 steps. Step 1, group by customer_id and date, and stringagg your article_id. That gets you the [A, C] you want for date 123.

Then using that query as that FROM for the outer query, you should be able to get what you're looking for. (or you could use a WITH clause if you prefer that format)

answered May 1, 2022 at 16:02

Bruce

1464 bronze badges

Collectives™ on Stack Overflow

Nested array in a SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related