MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();


VERSION()


8.0.30


SET @`json` := '
{
  "my_policies": {
    "inmy_policies": {
      "some_filters":
        [
          {"filter_name": "somefilter1", "filter_comm": []},
          {"filter_name": "macro", "filter_comm": "macro"},
          {"filter_name": "somefilter3", "filter_comm": []}
        ]
    }
  }
}
';
SELECT
  JSON_EXTRACT(
    @`json`,
    CONCAT(
      '$.my_policies.inmy_policies.some_filters[',
      (
        SELECT
          SUBSTRING_INDEX(
            SUBSTRING_INDEX(
              JSON_SEARCH(
                @`json`,
                'one',
                'macro',
                null,
                '$.my_policies.inmy_policies.some_filters[*].filter_name'
              ),
              "[",
              -1
            ),
            ']',
             1
          )
      ),
      ']'
    )
  ) `json_filter`;


json_filter


{"filter_comm": "macro", "filter_name": "macro"}