SQL Server JSON_QUERY Select Json object based on value

April 10, 2023

I have the following JSON data in a SQL Server table:

{
    "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
            }
        }
    ]
}

I am trying to grab the object based on the SourceFieldID.

I’ve tried a few variations of this, with no luck:

SELECT
    JSON_query(MapJSON, '$.Mapping')  
FROM
    MapHistories 
WHERE
    JSON_VALUE(MapJSON, '$.Mapping.SourceFieldID') = 1

Is this possible?

>Solution :

You can do it using OPENJSON to parses the json as the first argument and returns one or more rows containing data from the JSON objects :

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

Demo here