Passing a variable in OPENJSON WITH statement

Question

Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from API we have to specify '$.rates.GBP' in openjson statement to read the value of FX (last statement of below script). I am not able to pass this as a parameter to make SampleValue dynamic. I tried using dynamic SQL but did not succeed.

Please help.

SQL:

SELECT  *
FROM OPENJSON((select Json_table from @json), N'$')
WITH (   
      SampleValue nvarchar(max) '$.rates.GBP' ,   
      SampleDate DATE '$.date'
      ) ;

    /*DECLARE @SQL NVARCHAR(MAX) = 
    'SELECT  *  FROM OPENJSON((select Json_table from @json), N''$'') WITH (           SampleValue  nvarchar(max)  ''$.rates.GBP'',  SampleDate DATE ''$.date'' ) ;'

 select @SQL; 
 EXEC sp_executesql @SQL;

      DECLARE @SQLstring NVARCHAR(MAX) = 
'SELECT  *
FROM OPENJSON((select Json_table from @json), N''$'') WITH (   
      SampleValue nvarchar(max) '+ ''''+ @param + ''''+ ' ,   
      SampleDate DATE'  + ''''+ '$.date'  + '''' + ' ) ;'

      select @SQLstring ; 
EXEC sp_executesql @SQLstring ;*/

Answer 1

Try like below

select * into #Temp from @json
DECLARE @SQL NVARCHAR(MAX) = 
    'SELECT  *  FROM OPENJSON((select * from #Temp), N''$'') WITH (           SampleValue  nvarchar(max)  ''$.rates.GBP'',  SampleDate DATE ''$.date'' ) ;'

 select @SQL; 
 EXEC sp_executesql @SQL;