Advertisements
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