Magento 2: SQL get orders with specific option id

Question

I'm trying to get all orders with a specific option id to see what percentage of orders are ordered with measurement of centimeters.

Not very good with database queries and I can't seem to figure it out.

I've looked inside the sales_order_item table but it doesn't have an option_id column.

I need to find another way around this.

Answer 1

Using Database :-

Query :-

SELECT * FROM `sales_order_item` WHERE `product_options` LIKE '%2010%'

Replace 2010 with Your Option Id

---

In sales_order_item this table saved option id in product_options coumn with json Format.

You can decode this product_options And Apply Filter

json decode :-

json_decode($product_options)

Answer 2

The product options are saved as JSON.

With the MySQL version required by Magento 2, there seems to be no nice solution for this:

1. You can filter using LIKE "%2010%" or some more sophisticated expression to emulate JSON processing

2. As a second step you should json_decode the column within PHP to really check for the product_option in the JSON. That means 1. is just some kind of performance optimization - and not reliable.

Alternative:

If you MySQL version is a recent one, you can use JSON_EXTRACT.