Left join with JSON

I'm using PlanetScale (MySQL) and have 2 tables: orders and products.
Every order contains basic info about the ordered products (see screenshot).

Is there a way to do this?:
import { orders, products } from '$lib/server/db/schemas/products';

    .leftJoin(products, inArray(products.id, /*orders.products.map(({ id }) => id)*/)),

I would like it take the ids from the orders.products array and select those products.
Can you build this query in raw SQL?
No, not really, i'm a beginner. I tried searching for this but I don't know how to put it to words. Chat gpt tried helping and came up with this. Is this any accurate? I need at least a phrase which I can google to learn this.
Uhh, honestly I'm not sure either, I'm not very good with JSON functions
You might read about using the JSON functions in your dialect in general, maybe you'll find some references
Okay thank you