[SOLVED] Using param in SQL stored procedure select value

Using param in SQL stored procedure select value

I have a stored procedure that I am using to extract values from a JSON column in a table:

    @ReferenceID int,
    @BureausID int,
    @EmployersID int, 
    @FileType varchar(12),
    @TargetIndex int

Code:

SELECT 
    SalesOrderJsonData.* 
FROM 
    MapHistories AS Tab
CROSS APPLY 
    OPENJSON(Tab.MapJSON, N'$.Mapping') 
        WITH 
        (
            ReferenceNumber int N'$.ReferenceNumber',
            target_field nvarchar(max) N'$.TargetField[@TargetIndex].field', 
            output_field_description nvarchar(max),
            N'$.TargetField['@TargetIndex'].output_field_description',

I need to be able to pass in the target index, as there are multiple records in this JSON array. I am at a loss and not sure how to pass the param into the JSon path successfully.

Solution

You could pull out the whole TargetField array, and parse it using another call to OPENJSON with a dynamic path.

SELECT
  SalesOrderJsonData.* ,
  TargetData.*
FROM MapHistories AS Tab
CROSS APPLY OPENJSON(Tab.MapJSON, N'$.Mapping')
  WITH (
    ReferenceNumber int,
    TargetField nvarchar(max) AS JSON
  ) SalesOrderJsonData
CROSS APPLY OPENJSON(SalesOrderJsonData.TargetField, CONCAT(N'$[', @TargetIndex, N']'))
  WITH (
    target_field nvarchar(max) N'$.field', 
    output_field_description nvarchar(max)
-- etc