Search code examples
arraysjsonsql-serverparametersopen-json

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