SQL到JSON -SQL 2016中的对象数组到值数组

agxfikkp  于 2023-02-26  发布在  其他
关注(0)|答案(8)|浏览(157)

SQL 2016有一个新特性,可以将SQL Server上的数据转换为JSON。我在将对象数组组合为值数组时遇到了困难,即
示例-

CREATE TABLE #temp (item_id VARCHAR(256))

INSERT INTO #temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM #temp

--convert to JSON

SELECT (SELECT item_id 
FROM #temp
FOR JSON PATH,root('ids'))

结果-

{
    "ids": [{
        "item_id": "1234"
    },
    {
        "item_id": "5678"
    },
    {
        "item_id": "7890"
    }]
}

但我要的结果是-

"ids": [
        "1234",
        "5678",
        "7890"
    ]

有人能帮帮我吗?

jxct1oxe

jxct1oxe1#

谢谢!我们找到的解决方案是先转换成XML-

SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"' 
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER
mgdq6dx1

mgdq6dx12#

马丁!
我相信这是一个更简单的方法:

SELECT '"ids": ' + 
    REPLACE( 
      REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ),
      '"}','"' )
tjjdgumg

tjjdgumg3#

declare @temp table (item_id VARCHAR(256))

INSERT INTO @temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM @temp

--convert to JSON

select 
    json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]
from @temp
for json path

当我们想把字符串连接成json数组时,那么:
1.转义字符串- STRING_ESCAPE
1.使用逗号分隔符连接字符串- STRING_AGG,逗号ASCII代码为44
1.在括号中添加引号- QUOTENAME(无参数)
1.以json - JSON_QUERY形式返回字符串(包含元素数组)

ulydmbyx

ulydmbyx4#

由于基元值数组是有效的JSON,所以SQL Server的JSON功能中没有内置选择基元值数组的工具,这似乎有些奇怪(如果相反,这样的功能存在,至少在进行了大量搜索之后,我还没有发现它)。
上述方法的工作原理与描述相同,但是当应用于较大查询中的字段时,原语数组要用引号括起来。
例如,这个

DECLARE @BomTable TABLE (ChildNumber dbo.udt_ConMetPartNumber);
INSERT INTO @BomTable (ChildNumber) VALUES (N'101026'), (N'101027');
SELECT N'"Children": ' + REPLACE(REPLACE((SELECT ChildNumber FROM @BomTable FOR JSON PATH), N'{"ChildNumber":', N''), '"}','');

作品制作:

"Children": ["101026,"101027]

但是,按照上述方法,这:

SELECT
    p.PartNumber,
    p.Description,
    REPLACE(REPLACE((SELECT
                        ChildNumber
                     FROM
                        Part.BillOfMaterials
                     WHERE
                        ParentNumber = p.PartNumber
                     ORDER BY
                        ChildNumber
                    FOR
                     JSON AUTO
                    ), N'{"ChildNumber":', N''), '"}', '"') AS [Children]
FROM
    Part.Parts AS p
WHERE
    p.PartNumber = N'104444'
FOR
    JSON PATH

产生:

[
    {
        "PartNumber": "104444",
        "Description": "ASSY HUB           R-SER  DRIV HP10  ABS",
        "Children": "[\"101026\",\"101027\",\"102291\",\"103430\",\"103705\",\"104103\"]"
    }
]

其中 * Children * 数组被 Package 为字符串。

4uqofj5v

4uqofj5v5#

此版本(基于其他版本):

  • 正确转义特殊JSON字符(例如引号)
  • 如果没有数据,则返回空数组[]

需要SQL 2017或更高版本(由于STRING_AGG):

SELECT 
       CONCAT('[', 
            (SELECT STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', ',') 
             FROM #temp) 
        , ']')
r1zhe5dt

r1zhe5dt6#

下面是一个可能不实用的疯狂想法:使用JSON_MODIFY递归遍历数据集并将内容追加到JSON数组中:

with
  d (d) as (select * from (values (1),(2),(3),(4)) t (d)),
  j (d, j) as (
    -- Adapt the recursion to make it dynamic
    select 1, json_modify('[]', 'append $', d)
    from d
    where d = 1
    union all
    select d.d, json_modify(j, 'append $', d.d)
    from d join j on d.d = j.d + 1
  )
select * 
from j;

为了便于说明,我将其保持简单。当然,您将对其进行调整以使其具有动态性。这将产生:

|d  |j        |
|---|---------|
|1  |[1]      |
|2  |[1,2]    |
|3  |[1,2,3]  |
|4  |[1,2,3,4]|

甚至可用于模拟标准SQL JSON_ARRAYAGG

iszxjhcz

iszxjhcz7#

这些解决方案中的大多数实际上都是创建一个表示数组内容的CSV,然后将该CSV转换为最终的JSON格式。

DECLARE @tmp NVARCHAR(MAX) = ''

SELECT @tmp = @tmp + '"' + [item_id] + '",'
FROM #temp -- Defined and populated in the original question

SELECT [ids] = JSON_QUERY((
    SELECT CASE
        WHEN @tmp IS NULL THEN '[]'
        ELSE '[' + SUBSTRING(@tmp, 0, LEN(@tmp)) + ']'
        END
    ))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
lndjwyie

lndjwyie8#

我认为以下内容在SQL Server 2017中会更容易

select
 JSON_QUERY
 (
     '["' + STRING_AGG(t.item_id,'","') + '"]'
 ) as ids
 from #temp t
 for json auto, without_array_wrapper

相关问题