英文:
SQL Server JSON_QUERY Select Json object based on value
问题
以下是翻译好的内容：
我在SQL Server表中有以下JSON数据：
{
"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
}
}
]
}
我尝试根据SourceFieldID获取对象。
我尝试了几种变体，但都没有成功：
SELECTJSON_query(MapJSON, '$.Mapping')FROMMapHistoriesWHEREJSON_VALUE(MapJSON, '$.Mapping.SourceFieldID') = 1
这是可能的吗？
英文:
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:
SELECTJSON_query(MapJSON, '$.Mapping')FROMMapHistoriesWHEREJSON_VALUE(MapJSON, '$.Mapping.SourceFieldID') = 1
Is this possible?
答案1
得分: 1
使用OPENJSON来解析JSON作为第一个参数，并返回包含来自JSON对象的数据的一个或多个行：
SELECT SalesOrderJsonData.*FROM MapHistories AS TabCROSS APPLY OPENJSON(Tab.MapJSON, N'$.Mapping') WITH (SourceFieldId int N'$.SourceFieldId',TargetField nvarchar(max) N'$.TargetField' AS JSON) AS SalesOrderJsonDatawhere SalesOrderJsonData.SourceFieldId = 46
英文:
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 TabCROSS APPLY OPENJSON(Tab.MapJSON, N'$.Mapping') WITH (SourceFieldId int N'$.SourceFieldId',TargetField nvarchar(max) N'$.TargetField' AS JSON) AS SalesOrderJsonDatawhere SalesOrderJsonData.SourceFieldId = 46
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论