我有以下SQL:
SELECT
f.id ,
f.active,
f.name,
pages.id,
pages.name,
pages.title,
pages.storage_key,
pages.next_page,
[elements].id,
[elements].name,
[elements].new_line,
[elements].required,
[elements].title,
[type].name,
--width,
meta_data.form_step,
meta_data.id,
meta_data.name,
meta_data.sub_title,
meta_data.title
FROM Forms f
INNER JOIN Pages pages ON pages.form_id = f.id
INNER JOIN [Elements] [elements] ON [elements].page_id = pages.id
INNER JOIN ElementTypes [type] ON [type].id = [elements].type
INNER JOIN ElementWidths [width] ON [width].id = [elements].width
LEFT JOIN MetaData meta_data ON meta_data.element_id = [elements].id
WHERE f.id = 1 -- checks against the "formId" which will always be passed
AND pages.id = ISNULL(null,pages.id) -- checks wether the "pageId" is NULL, if NULL then just use the "formId" in the WHERE
AND pages.index_page = CASE WHEN null is null THEN 1 else pages.index_page end
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
[类型].名称没有正确返回json,json应该是这样的:
{
"id": 1,
"active": true,
"name": "form1",
"pages": [
{
"id": "05a2d9bb-5494-4cb1-889d-b87d908aef16",
"title": "Ticket creation",
"storage_key": "ticket_creation",
"next_page": "7ac90f99-eb58-404e-a0c7-d9627af177d7",
"elements": [
{
"id": 1,
"new_line": true,
"required": true,
"title": "What type of ticket would you like to create?",
"type": "choice_field",
"meta_data": [
{
"form_step": "7ac90f99-eb58-404e-a0c7-d9627af177d7",
"id": 1,
"sub_title": "Something is broken or not working as expected"
},
{
"form_step": "66daf295-3473-4025-9768-9fd8caf81f98",
"id": 2,
"sub_title": "I would like to do something..."
}
]
}
]
}
]
}
但是,用于JSON自动的sql正在围绕"type"包装一个数组,如下所示:
{
"id": 1,
"active": true,
"name": "form1",
"pages": [
{
"id": "05a2d9bb-5494-4cb1-889d-b87d908aef16",
"title": "Ticket creation",
"storage_key": "ticket_creation",
"elements": [
{
"id": 1,
"new_line": true,
"required": true,
"title": "What type of ticket would you like to create?",
"type": [
{
"name": "choice_field",
"meta_data": [
{
"form_step": "7ac90f99-eb58-404e-a0c7-d9627af177d7",
"id": 1,
"sub_title": "Something is broken or not working as expected"
},
{
"form_step": "66daf295-3473-4025-9768-9fd8caf81f98",
"id": 2,
"sub_title": "I would like to do something..."
}
]
}
]
}
]
}
]
}
"type"值来自此处的查找表:
为什么json包装"type"用作数组?我只想要"type":"choice_field"来自查找表?