How to correctly compose a query to search for a JSON field in mySql?

Question

MySQL

there is a table 'action'. it has a field of type JSON 'condition'.
data structure in this field

[{
    "data": [{"operator": "1"},{"type": "1"},{"1": {"values": "3"}}],
    "action": "6",
    "coupon": ["130"],
    "discount": "10",
    "change_cart": "1",
    "discount_type": "0",
    "change_price_after_use": "0",
    "show_in_additional_items": "1"
  },
  {
    "data": [{"operator": "1"},{"type": "2"},{"2": {"values": "77"}}],
    "action": "6",
    "coupon": ["130"],
    "discount": "200",
    "change_cart": "0",
    "discount_type": "1",
    "change_price_after_use": "1",
    "show_in_additional_items": "1"
  },
  {
    "data": [{"operator": "1"},{"type": "3"},{"3": {"values": "151262"}}],
    "action": "6",
    "coupon": ["130"],
    "discount": "10",
    "change_cart": "1",
    "discount_type": "0",
    "change_price_after_use": "0",
    "show_in_additional_items": "0"
  }
]

The question is, how to make a correct query to select all records that have "action": "6" in the 'condition' field?

Answer 1

Alternatively, use LIKE:

SELECT * FROM `action` WHERE `condition` LIKE '%"action": "6"%';

Test SQL query online

Answer 2

query to select all records that have "action": "6" in the 'condition' field

SELECT DISTINCT action.*
FROM action
CROSS JOIN JSON_TABLE(action.`condition`,
                      '$[*].action' COLUMNS (action INT PATH '$')) jsontable
WHERE jsontable.action = 6

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c3e97c...