Add a subquery in a WHERE condition, using hook_views_query_alter() I tried to add a subquery like this:

function mymodule_views_query_alter(&amp;$view) {
  $view-&gt;query-&gt;where[1]['conditions'][] = array(
    'field' =&gt; 'node.nid',
    'value' =&gt; 'SELECT SUBSTRING(source, 6) FROM {url_alias} WHERE alias LIKE "pros/%"',
    'operator' =&gt; 'in',
  );
}


I have a specific case where nodes are created with aliases starting with pros/ or not, and I want to get only them.

In a simple raw SQL query, I'd go with this:

SELECT *
FROM node node
WHERE node.nid IN (SELECT SUBSTRING(source, 6)
                   FROM {url_alias}
                   WHERE alias LIKE "pros/%")


But I can't find out how to do this using hook_views_query_alter().
Ideally I think its right to use views_join_subquery. But the subquery you are using is with IN keyword is always slow and will produce same result as using a LEFT JOIN joining with node.nid field. 

You can try creating a views_join and adding the url_alias table by add_field method while the node table will be the left table. 

I don't have the code at the moment but you can have a look at the below links. 
https://stackoverflow.com/questions/6851945/add-table-join-where-and-order-by-to-views-query-in-views-query-alter

http://www.wordpressecoder.com/2014/01/how-to-alter-views-query-with-multiple.html 
http://www.yellowpencil.com/blog/last-ditch-fix-programmatically-changing-drupal-7-view