Using param in SQL stored procedure select value

Question

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.

you can probably do a nested openjson on targetfield and then add a WHERE secondAlias.[key] = @TargetIndex — siggemannen, Aug 25 '23 at 17:24

score 2 · Accepted Answer · answered Aug 25 '23 at 17:27

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

Using param in SQL stored procedure select value

1 Answers1