Change upload URL by mime type

It turns out the value that points to the PDFs’ locations on disk is stored in the wp_postmeta table, under the meta_value field.

For example a media item located at site.com/app/uploads/2019/06/whatever.pdf would have a meta_value value of 2019/06/whatever.pdf. To change the location of all PDFs I first found the IDs of all PDFs (from the ID column in the wp_posts table, selecting where post_mime_type="application/pdf) and then, for each of those IDs, found the corresponding row in the wp_postmeta table using the post_id column and updated that row”s meta_value field, replacing the year/month/ with pdf/.

I used a PHP script:

$ids = $wpdb->get_results( "SELECT * FROM `wp_posts` WHERE post_mime_type="application/pdf"" );
foreach ( $ids as $id ) {
    $pdf = $wpdb->get_results( "SELECT * FROM `wp_postmeta` WHERE post_id=$id->ID" );
    $oldpdf = $pdf[0]->meta_value;

    $_tmp = explode("https://wordpress.stackexchange.com/",  $pdf[0]->meta_value);
    if ( sizeof($_tmp) > 2 ) {
        unset( $_tmp[0], $_tmp[1] );
        $newpdf="pdf/".implode("https://wordpress.stackexchange.com/", $_tmp);
    }
    else {
      $newpdf = $oldpdf;
    }

    if ( $oldpdf != $newpdf ) {
        rename( wp_get_upload_dir()['basedir']."https://wordpress.stackexchange.com/".$oldpdf, wp_get_upload_dir()['basedir']."https://wordpress.stackexchange.com/".$newpdf );
        $wpdb->update(
            'wp_postmeta',
            [ 'meta_value' => $newpdf ],
            [ 'post_id' => $id->ID ]
        );
    }
}