how to create a proper query for getting a list of users with taxonomy related meta key

Objective

I want to list sp_provider (hospital,clinics,doctors) whose sub_category is suppose 'neurology'

rephrasing the words:

I want to list users with sp provider category(meta value) such as
“hospital” and the user have a sub category(meta value) “neurology”

• sp_provider is a meta value in user profile (sp-provider maybe a post-type or something for user management screen to edit)
• sub category is also a meta value stored in user profile, the list is believed to link up to post type sub_categories with taxonomy sub_category

List out all the users with selected sp provider, sub category and the user account is verified and activated

The steps to create proper query

• convert the selected terms ID to slug
• create the query based on terms slug

Notes on your meta data and caution notes
Your sub_category data is serialized. I believed that it is an array and for ease of handling, the whole array is being serialized before storing. It make it difficult to operate such as searching. To accurately handle it is to unserialize it first. In some cases such as yours, if it is for read only purpose without any saving operation, it would be fine and your data is a simple array of values.

Speaking of the data, it is still safe although it is not the best choice. If it involves data update, then it would be very dangerous because a serialized data is very easy to break. It will render your data, at least that field completely into useless state or if error handling is not enough, it will break your php, some worse cases will even rendering a blank page which is not rare when the template relies on the data to output template. You may take it into account.

Notes on query keyword ‘IN’

// IN does not work when the stored data is a serialized array, originally IN work for array() when comparing a list of data to your provided array.

// $meta_query_args[] = array(
//     'key' => 'sub_category',
//     'value' => array( 'cardiology', 'neurology', 'oncology' ),
//     'compare' => 'IN'
// );

The following list users based on category and sub category provided that user is verified and activated

// suppose you get a value somewhere
$cat_sepration = array(
    "48", "50"
);

$query_args = array(
    // 'post_type' => 'sp-provider', // ignored by user query, it is for post query
    'role__in' => array('professional', 'business'),
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
);

// prepare term slug from term id for searching
// because the $cat_separation is a list of ID, you need to convert to slugs before putting into user query to search
$terms = get_terms( array(
    'taxonomy' => 'sub_category',
    'include' => $cat_sepration, // it takes the selected id here and convert to slugs
    'fields' => 'slugs',
    'hide-empty' => false,
) );

// check point
var_dump( $terms ); // a list of terms slug for use to search for user in related category 

$meta_query_args = array();

// Verify user
$meta_query_args[] = array(
    'key' => 'verify_user',
    'value' => 'on',
    'compare' => '='
);

// active users filter
$meta_query_args[] = array(
    'key' => 'activation_status',
    'value' => 'active',
    'compare' => '='
);

// sp category
// since your data is single value, IN will also work
$meta_query_args[] = array(
    'key' => 'spcategory_search',
    'value' => array( 'Hospital', 'Clinic' ), // case sensitive, array is for multiple
    'compare' => 'IN' // = is for one value, IN is for multiple
);

// ** this is how to the `category query` is properly created
// sub category
// the following can search the serialized data in database
// this comparing method using LIKE operator is similar to those used in role__in comparison in WordPress Core
$category_query_args = [];
foreach ($terms as $key => $term) {
    $category_query_args[] = array(
        'key' => 'sub_category',

// without :" is also work, :" increase the accuracy because it relies on the LIKE operator comparing to the serialized data. A text inside serialized data is beginning with :"something" so it helps to match

        'value' => ':"' . $term, 
        'compare' => 'LIKE',
    );
}

$meta_query_args[] = array(
    array(
        // to ensure the user have all the categories at the same time, so the operator is AND
        'relation' => 'AND',
        $category_query_args,

        // the following is proved to work in test data, it is written as a foreach above for programatic reason

        // here is an expanded version of $category_query_args (the above for-each loop for reference)
        // because 'LIKE' is used, it searches based on similar keywords
        // array(
        //  'key' => 'sub_category',
        //  'value' => 'cardiology', // or ':"cardiology', means beginning with :"cardiology
        //  'compare' => 'LIKE',
        // ),
        // array(
        //  'key' => 'sub_category',
        //  'value' => 'neurology',
        //  'compare' => 'LIKE',
        // ),
        // array(
        //  'key' => 'sub_category',
        //  'value' => 'Oncology',
        //  'compare' => 'LIKE',
        // )
    ),
);

if (!empty($meta_query_args)) {
    $query_relation = array('relation' => 'AND',);
    $meta_query_args = array_merge($query_relation, $meta_query_args);
    $query_args['meta_query'] = $meta_query_args;
}
$user_query = new WP_User_Query($query_args);

// for debugging purpose
// var_dump($query_args);

// check point
var_dump($user_query->request); // you may confirm the SQL query and run in sql platform/tools

$users = $user_query->get_results(); // if result is result, you may do any operation hereafter

// check point
foreach ($users as $key => $user) {
    print_r(get_user_meta($user->ID)); // confirm the meta value
}

// each of the above step proved to be working with simulated data