OPENJSON running once per row of an INNER JOIN unnecessarily?
I ended up having to solve this by creating a table variable to hold the results of OPENJSON and joining on that variable.  This forces SQL Server to actually create a temp table containing the query data rather than repeatedly running OPENJSON.  Apparently a CTE doesn't do that, even though I thought it did.  Presumably this applies to any table-valued function, actually.
This works, and results in OPENJSON only being run once (when deriving the data to fill the table variable with):
DECLARE @jsonQueryText nvarchar(max) = '[{&quot;UniqueId&quot;:&quot;0xy-5244249247924974924C&quot;,&quot;MobileNo&quot;:null,&quot;Email&quot;:&quot;joe.bloggs@domain.co.uk&quot;},{...},...]';
DECLARE @Qry TABLE(UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));
INSERT INTO @Qry SELECT * FROM OPENJSON(@jsonQueryText) WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));

SELECT
    usr.[user_id],
    usr.[firstname],
    usr.[surname],
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[mp_user] usr
    INNER JOIN @Qry qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])