Home > Software design >  Nested array in a SQL query
Nested array in a SQL query

Time:05-02

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.

CodePudding user response:

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)

CodePudding user response:

what about this? Aggregate twice :)

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

enter image description here

  • Related