EXPLAIN SELECT

    *

FROM

    `orders`

WHERE

    amazon_order_id IN (

        SELECT

            j.azid

        FROM

            marketplace_order_data m,

            JSON_TABLE(

                m.shipment_order_ids,

                '$[*]' COLUMNS(azid varchar(20) PATH '$')

            ) AS j

        WHERE

            shipment_id IN ('XXX')

    );

+------+-------------+------------+-------+---------------+------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+

| id   | select_type | table      | type  | possible_keys | key        | key_len | ref   | rows   | Extra                                                                                               |

+------+-------------+------------+-------+---------------+------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+

|    1 | PRIMARY     | m          | const | shipmentId    | shipmentId | 98      | const | 1      |                                                                                                     |

|    1 | PRIMARY     | orders | ALL   | NULL          | NULL       | NULL    | NULL  | 599922 |                                                                                                     |

|    1 | PRIMARY     | j          | ALL   | NULL          | NULL       | NULL    | NULL  | 40     | Table function: json_table; Using where; FirstMatch(orders); Using join buffer (flat, BNL join) |

+------+-------------+------------+-------+---------------+------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+

EXPLAIN SELECT

    amazon_order_id

FROM

    `orders`

WHERE

    amazon_order_id IN (

        SELECT

            j.azid

        FROM

            marketplace_order_data,

            JSON_TABLE(

                shipment_order_ids,

                '$[*]' COLUMNS(azid varchar(20) PATH '$')

            ) AS j

        WHERE

            shipment_id IN ('XXX')

    );

+------+-------------+----------------------------+-------+---------------+-----------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+

| id   | select_type | table                      | type  | possible_keys | key             | key_len | ref   | rows   | Extra                                                                                               |

+------+-------------+----------------------------+-------+---------------+-----------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+

|    1 | PRIMARY     | marketplace_order_data | const | shipmentId    | shipmentId      | 98      | const | 1      |                                                                                                     |

|    1 | PRIMARY     | orders                 | index | NULL          | amazon_order_id | 188     | NULL  | 599930 | Using index                                                                                         |

|    1 | PRIMARY     | j                          | ALL   | NULL          | NULL            | NULL    | NULL  | 40     | Table function: json_table; Using where; FirstMatch(orders); Using join buffer (flat, BNL join) |

+------+-------------+----------------------------+-------+---------------+-----------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+