There are often several ways to derive a cardinality estimate, with each method giving a different (but equally valid) answer. That is simply the nature of statistics and estimations.
You ask essentially why one method produces an estimate of 296.839 rows, while another gives 181.8 rows.
Let's look at a simpler example of the same AdventureWorksDW2017 join ...
You should directly extract the values from the json array using the WITH clause, like this:
FROM json_test src
CROSS APPLY OPENJSON(src.the_json , 'lax $')
[name] sysname '$.name'
, [object_id] int '$.object_id'
You can't, as far as I'm aware.
One might try things like:
OPTION (TABLE HINT (sp, READUNCOMMITTED))
...but this will fail for things that aren't real tables.
Table-valued or OPENROWSET function 'sp' cannot be specified in the TABLE HINT clause.
It would also fail because the desired hint is semantic-affecting.
If the thing you need to access doesn't ...
You need better indexing, and a bit of a rewrite.
Because you’re only using one table, you can use ROW_NUMBER() in a sub-query and then grab the top row from each partition.
SELECT grp_fk_obj_id, grp_name, grp_id
SELECT grp_fk_obj_id, grp_name, grp_id, ROW_NUMBER() OVER (PARTITION BY grp_fk_obj_id ORDER BY date_from DESC, ISNULL(date_to, '4000-01-...
This is a bug in the way SQL Server decides whether a rebind is required with certain plan shapes. It has been in the product since SQL Server 2005.
This uses a trace flag to force a spool:
CREATE TABLE #T (v integer NOT NULL);
INSERT #T VALUES (1), (2), (3), (4);