Nested array in a SQL query

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.

Answer

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)