In the query profile UI in snowsight each node shows its own node execution time. But SYSTEM$EXPLAIN_PLAN_JSON does not include this information. Is there a way of obtaining the same in a more usable form than the UI?
SYSTEM$EXPLAIN_PLAN_JSON shows the plan, not the actual execution of a query. Since it's showing the plan it does not have statistics on how long each row operation take in time.
There is a more recent public preview feature that provides programmatic access to the information used in the query profile UI. You can read the announcement here: https://www.snowflake.com/blog/programmatic-access-query-profile-public-preview/
It is a table function, so it's called like this:
select * from table(GET_QUERY_OPERATOR_STATS('01ab99bf-0403-42d2-0016-db0300e81086'));
You can read in the documentation here:
https://docs.snowflake.com/sql-reference/functions/get_query_operator_stats
This does provide time estimates. It's important to know that they're estimates because several row operations can be (and usually are) happening simultaneously. The estimates try to apportion how much time was spent in each based on things like wait states, CPU statistics, etc.
Edit: The get_query_operator_stats table function returns the times in a JSON property named overall_percentage. In order to convert that to time, you'll need to get the time it spent running in the warehouse (total duration minus compile and misc non warehouse activities). You can get that from the query_history table function if it's within the last 7 days: https://docs.snowflake.com/en/sql-reference/functions/query_history. If the query completed or terminated more than about 30 minutes ago and less than a year ago, you can get this from the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view.