SQL生成不带方括号的JSON [英] SQL generate JSON without square brackets

问题描述

我有这个查询:

SELECT 
    'Ball' AS title, 'Blue' AS color, 
    (SELECT '1' AS Opt1, '2' AS Opt2 
     FOR JSON PATH) AS 'Options'
FOR JSON PATH, ROOT('product')

我得到了这个非常好的 JSON，但是在 product(标记)之后我不能有括号

And I get this JSON which is quite good but I can't have brackets after product (marked)

{"product": 
[  <======
{
 "title": "Ball",
 "color": "Blue",
 "Options": [{"Opt1": "1","Opt2": "2"}]
}
]  <======
}

换句话说，我需要这个:

In other words I need this:

{"product": 
{
 "title": "Ball",
 "color": "Blue",
 "Options": [{"Opt1": "1","Opt2": "2"}]
}
}

我无法使用 WITHOUT_ARRAY_WRAPPER 选项，因为我已经使用了 ROOT 选项.我应该怎么做才能删除这些方括号?

I can't use the WITHOUT_ARRAY_WRAPPER option because I already use ROOT option. What should I do to remove these square brackets?

推荐答案

非常接近 Greg Low 的答案，但使用 json_query 包装内部选择以避免自动转义(这是因为选项 without_array_wrapper 使用):

Very close to Greg Low's answer, but wrapping the inner select with json_query to avoid automatic escape (which happens because the option without_array_wrapper is used):

SELECT JSON_QUERY((
    SELECT 'Ball' AS title, 
           'Blue' AS color, (
                select '1' AS Opt1, 
                       '2' AS Opt2 
                FOR JSON PATH
           ) as Options
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS Product
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

这会给你想要的结果 -

This gives you the desired result -

{
  "Product": {
    "title": "Ball",
    "color": "Blue",
    "Options": [
      {
        "Opt1": "1",
        "Opt2": "2"
      }
    ]
  }
}

这篇关于SQL生成不带方括号的JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！