BigQuery: Order by in subselect alternative

Question

For an application I'm building im trying to get all the items in an order in a single column as a 'Stringed' Array. I have solved that the problem I'm having is that I want those items in a certain order (item_order) however when I do the subselect they are in random order and I can't order by inside a subselect. So what alternatives do I have to my current approach?

Current query:

select 
  order_id, 
  order_number, 
  client_desc, 
  array_to_string(
      array_reverse(
        array(
          select concat(item_order, ' - ', item_id , ' - ', item_desc, '\n ', coalesce(item_details, ''))
          from expertis_wm.items as item
          where orders.order_id = order_id
      )), 
    '\n ') as products
from expertis_wm.orders as orders

sample data

Orders:
order_id, order_number, client_desc
7, 'ON0005', 'Apple Inc'


Items:
order_id, item_order, item_id, item_desc, item_details
7, 1, 456, 'Laptop', '15" 16 GB RAM Laptop '
7, 2, 431, 'Tablet', 'Samsung Pad 5',
7, 3, 245, 'Phone', 'Nokia C500'

Current Result: (Note that the order in the products is not correct, 3 first then 1 then 2)
order_id, order_number, client_desc, products
7, 'ON0005', 'Apple Inc', '3 -  245 - Phone\n Nokia C500\n 1 - 456 - Laptop\n 15" 16 GB RAM Laptop\n 2 - 431 - Tablet\n Samsung Pad 5\n '

Desired Result: (
order_id, order_number, client_desc, products
7, 'ON0005', 'Apple Inc', '1 - 456 - Laptop\n 15" 16 GB RAM Laptop\n 2 - 431 - Tablet\n Samsung Pad 5\n 3 -  245 - Phone\n Nokia C500\n '

I don't know what alternatives I have but any direction in solving this problem will be greatly appreciated!

Answer 1

Consider below approach

select any_value(o).*,
  array_agg(
    concat(item_order, ' - ', item_id , ' - ', item_desc, '\n ', coalesce(item_details, ''))
    order by item_order
  ) products
from Orders o 
left join Items i 
using(order_id)
group by format('%t', o)           

if applied to sample data in your question - output is