当前位置:优学网  >  在线题库

使用FOR JSON自动包装不需要的元素

发表时间:2022-07-21 00:20:13 阅读:164

我有以下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"来自查找表?

🎖️ 优质答案
相关问题