SQL Server 2016 - 如何从JSON中选择整数数组

Question

SQL Server 2016 - 如何从JSON中选择整数数组

5

我从客户端收到了一个有效的JSON字符串，它包含一个整数值数组：

declare @JSON nvarchar(max) = N'{"Comments": "test", "Markets": [3, 151]}'

如何正确选择市场ID？

如果我使用以下查询：select * from openjson(@JSON) j，它会返回

市场类型为4，意味着它是一个对象，但下面的查询返回了空值：
select j.Markets from openjson(@JSON) with(Markets nvarchar(max)) j

我的目标是根据这些ID更新“Market”表，例如：
update Market set Active = 1 where MarketID in (3, 151)

有没有办法做到这一点？可以使用与SQL Server 2016兼容的任何内置函数。

注意:
感谢@johnlbevan的帮助。SELECT VALUE FROM OPENJSON(@JSON, '$.Markets')对于这个问题非常有效。

为了完整起见，这是我如何从SQL Server创建JSON整数数组("Markets": [3, 151])的方法。
由于2016年没有array_agg函数，所以我这样做：

SELECT (
  JSON_QUERY('[' + STUFF(( SELECT ',' + CAST(MarketID AS VARCHAR)
  FROM Market
  FOR XML PATH('')),1,1,'') + ']' ) AS Markets)

- FisNaN

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JohnLBevan · Accepted Answer

要将Markets数组与其他列一起扩展，可以这样做：

SELECT Comments, Market
FROM OPENJSON('{"Comments": "test", "Markets": [3, 151]}')
WITH (Comments nvarchar(32), Markets NVARCHAR(MAX) AS JSON) AS a
CROSS APPLY OPENJSON (a.Markets) WITH (Market INT '$') AS b

将字符串转换为JSON格式
将返回的第一个字段映射到类型为nvarchar(32)的Comments列
将第二个字段映射到类型为nvarchar(max)的Markets列，然后使用 as json 表示其内容是JSON格式（请参见https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql#arguments 以获取更详细的说明 - 搜索页面中的as json; 该关键段落从第4个出现开始）
使用cross apply将OPENJSON函数应用于Markets列，以便我们可以从该属性中获取值。
最后使用WITH语句将名称Market映射到返回的值，并将其分配为INT数据类型。

但是，要获取更新所需的值列表，您可以执行以下操作：

UPDATE Market
SET Active = 1
WHERE MarketID IN
(
    SELECT value
    FROM OPENJSON('{"Comments": "test", "Markets": [3, 151]}','$.Markets')
);

再次使用OPENJSON可以将字符串作为JSON查询
不过这次我们指定一个路径直接指向Markets的值（参见https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql）
现在我们返回所返回的value并过滤我们的UPDATE，就像处理任何其他子查询一样。