SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table MapHistories (
  MapJSON nvarchar(max)
);
insert into MapHistories values
('{"Mapping": [
        {
            "SourceFieldId": 46,
            "TargetField": {
                "output_field_description": "Last Name",
                "output_header_field": "Last Name",
                "required": "Y",
                "data_type": "TEXT",
                "date_format": "",
                "number_of_decimals": "",
                "max_length": 30,
                "field": 1
            }
        },
        {
            "SourceFieldId": 46,
            "TargetField": {
                "output_field_description": "Last Name",
                "output_header_field": "Last Name",
                "required": "Y",
                "data_type": "TEXT",
                "date_format": "",
                "number_of_decimals": "",
                "max_length": 30,
                "field": 1
            }
        }
]}');
1 rows affected

SELECT SalesOrderJsonData.*
FROM MapHistories AS Tab
CROSS APPLY OPENJSON(Tab.MapJSON, N'$.Mapping') WITH (
    SourceFieldId int N'$.SourceFieldId',
    TargetField nvarchar(max) N'$.TargetField' AS JSON 
    ) AS SalesOrderJsonData
where SalesOrderJsonData.SourceFieldId = 46


SourceFieldId
TargetField


46
{
                "output_field_description": "Last Name",
                "output_header_field": "Last Name",
                "required": "Y",
                "data_type": "TEXT",
                "date_format": "",
                "number_of_decimals": "",
                "max_length": 30,
                "field": 1
            }

46
{
                "output_field_description": "Last Name",
                "output_header_field": "Last Name",
                "required": "Y",
                "data_type": "TEXT",
                "date_format": "",
                "number_of_decimals": "",
                "max_length": 30,
                "field": 1
            }

SourceFieldId	TargetField
46	{ "output_field_description": "Last Name", "output_header_field": "Last Name", "required": "Y", "data_type": "TEXT", "date_format": "", "number_of_decimals": "", "max_length": 30, "field": 1 }
46	{ "output_field_description": "Last Name", "output_header_field": "Last Name", "required": "Y", "data_type": "TEXT", "date_format": "", "number_of_decimals": "", "max_length": 30, "field": 1 }