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.


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 :-

| improve this answer | |
  • I need to get the amount of order items with that specific option id, is there no way of doing that through the database? – Howliee Sep 4 '19 at 9:52
  • you mean filter in database ? – Rk Rathod Sep 4 '19 at 9:53
  • Yes, sorry I'm not very good with databases – Howliee Sep 4 '19 at 9:54
  • ok let me check. – Rk Rathod Sep 4 '19 at 9:55
  • check updated answer – Rk Rathod Sep 4 '19 at 9:58

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.


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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.