A REPEATED column holds an array: orders.items is a list of records, one per product, each with sku, qty and unit_price. To work with the items as rows, UNNEST the array and join it to its row:
SELECT o.order_id, item.sku, item.qty
FROM shop.orders AS o, UNNEST(o.items) AS item;
The comma is a correlated cross join: each order joins to its own items. CROSS JOIN UNNEST(...) means the same.
An order with an empty array produces no rows that way. Use LEFT JOIN UNNEST(o.items) AS item to keep it, with NULLs.
For a single element, items[OFFSET(0)] is the first (counting from 0) and items[ORDINAL(1)] is the first counting from 1. ARRAY_LENGTH(items) counts them, and 'LC-ESP-HSE' IN UNNEST(skus) tests whether a value is in an array.