Wordpress - Random sort within an already sorted query

There is no need to do an overrated amount of queries to accomplish this. you can still use only one query, the_posts filter and some PHP to sort your code as needed.

I take that this is a custom query from what I read in your question, so we can do the following:

• First, we want to introduce a custom WP_Query parameter so that we can use that parameter as a trigger for our the_posts filter. We will call this parameter n wpse_custom_sort and it will take a value of true in order to trigger the filter

• Next, we will use the the_posts filter to sort the posts according to custom field, then sort those posts randomly per custom field and finally return the sorted posts

THE CODE

Just a few notes though

• The code is untested and needs at least PHP 5.4

• I have used get_post_meta() to return the custom field values per post, as you are using ACF, you might need to adjust this to use get_field(). I'm not familiar with ACF, so you will need to sort that part. The logic would still remain the same though

• Just remember, querying custom fields does not result in extra queries as they are cached, so this is a very lean, optimized way to accomplish your end goal

Here is the code for the custom query. All you basically need to do is to add the extra parameter to your query args in your custom page template

// Run your query normally
$args = [
    'wpse_custom_sort' => true, // This parameter will trigger or the_posts filter
    // Your query args here
];
$q = new WP_Query( $args );

// Your loop as normal

Now, we will run our filter (this goes into functions.php or preferably into a custom plugin)

/**
 * Function to flatten a multidimentional array (for later use)
 * 
 * Credit to zdenko
 * @link https://gist.github.com/kohnmd/11197713
 */
function flatten_array( $arg ) 
{
    return is_array( $arg ) 
        ? 
        array_reduce( $arg, function ( $c, $a ) 
            { 
                return array_merge( $c, flatten_array( $a ) ); 
            }, [] ) 
        : 
        [$arg];
}

// The the_posts filter
add_filter( 'the_posts', function ( $posts, $q )
{
    // First we need remove our filter
    remove_filter( current_filter(), __FUNCTION__ );

    // Check if our custom parameter is set and is true, if not, bail early
    if ( true !== $q->get( 'wpse_custom_sort' ) )
        return $posts; 

    // Before we do any work, and to avoid WSOD, make sure that the flatten_array function exists
    if ( !function_exists( 'flatten_array' ) )
        return $posts;

    // Our custom parameter is available and true, lets continue
    // Loop through the posts
    $major_array = [];
    foreach ( $posts as $p ) {
        $meta = get_post_meta(
            $p->ID,
            'pkg_id',
            true
        );

        // Bail if we do not have a $meta value, just to be safe
        if ( !$meta )
            continue;

        // Sanitize the value
        $meta = filter_var( $meta, FILTER_SANITIZE_STRING );

        // Lets build our array
        $major_array[$meta][] = $p;
    }

    // Make sure we have a value for $major_array, if not, bail
    if ( !$major_array )
        return $posts;

    // Lets randomize the posts under each custom field
    $sorted = [];
    foreach ( $major_array as $field ) 
        $sorted[] = shuffle( $field );

    // Lets flatten and return the array of posts
    $posts = flatten_array( $sorted );

    return array_values( $posts );
}, 10, 2 );

If we use:

$args = array( 
    'post_type'      => 'custom_post_type',
    'post_status'    => 'publish',
    'posts_per_page' => 9,
    'meta_key'       => 'pkg_id',
    'orderby'        => array( 'pkg_id' => 'ASC', 'rand' => 'DESC' )
);

then we get the order part as:

ORDER BY wp_postmeta.meta_value ASC, RAND() DESC

This seems to work, but the DESC is not needed.

But note that ordering by RAND() doesn't scale well. The method by @PieterGoosen might be better suited for you in that case.