英文:
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获取对象。
我尝试了几种变体，但都没有成功：
SELECT
JSON_query(MapJSON, '$.Mapping')
FROM
MapHistories
WHERE
JSON_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:
SELECT
JSON_query(MapJSON, '$.Mapping')
FROM
MapHistories
WHERE
JSON_VALUE(MapJSON, '$.Mapping.SourceFieldID') = 1
Is this possible?
答案1
得分: 1
使用OPENJSON来解析JSON作为第一个参数，并返回包含来自JSON对象的数据的一个或多个行：
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论