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

I received a valid JSON string from client side, it contains an array of integer values:

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

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

How to select the market IDs correctly?

如何正确选择市场id ?

If I use a query like this: select * from openjson(@JSON) j, it returns

如果我使用这样的查询:select * from openjson(@JSON) j，它返回。

The type of Markets is 4, which means an object,
but the query below returns null value:
select j.Markets from openjson(@JSON) with(Markets nvarchar(max)) j

市场的类型是4，这意味着一个对象，但是下面的查询返回空值:select j。从openjson(@JSON)到(Markets nvarchar(max))) j的市场

My goal is to update Market table based on these IDs, eg:
update Market set Active = 1 where MarketID in (3, 151)

我的目标是基于这些id更新市场表，例如:update Market set Active = 1，其中MarketID在(3,151)

Is there a way to do this?
Any built-in function compatible with SQL server 2016 can be used.

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

Note:
Thanks to @johnlbevan
SELECT VALUE FROM OPENJSON(@JSON, '$.Markets') works perfectly for this problem.

注意:感谢@johnlbevan从OPENJSON(@JSON，“$. markets”)中选择值，它非常适合这个问题。

Just for the completeness, here is how I created the JSON integer array ("Markets": [3, 151]) from SQL server.
Since there is no array_agg function out of the box in 2016, I did this:

为了完整起见，下面是我如何从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)

1 个解决方案

#1

To expand the Markets array alongside other columns you can do this:

要将市场数组与其他列一起展开，您可以这样做:

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

Convert the string to json
将字符串转换为json
Map the first field returned to the Comments column with type nvarchar(32)
将返回的第一个字段映射到Comments列，类型为nvarchar(32)
Map the second field to Markets column with type nvarchar(max), then use as json to say that the contents is json (see https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql#arguments for a more detailed description - search the page for as json; the key paragraph starts at the 4th occurrence)
将第二个字段映射到带有nvarchar(max)类型的Markets列，然后使用json表示内容为json(参见https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql#参数以获得更详细的描述——搜索页面为json;关键段落从第四个事件开始)
Use a cross apply to apply the OPENJSON function to the Markets column so we can fetch values from that property.
使用cross apply将OPENJSON函数应用到Markets列，这样我们就可以从该属性中获取值。
Finally use the WITH statement to map the name Market to the returned value, and assign it a data type of INT.
最后，使用WITH语句将名称市场映射到返回值，并为其分配INT类型的数据类型。

However, to just get the list of values needed to do the update, you can do this:

但是，要获得更新所需的值列表，可以这样做:

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

Again OPENJSON lets us query the string as JSON
同样，OPENJSON让我们可以将字符串查询为JSON。
However this time we specify a path to point at the Markets value directly (see https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql)
然而，这一次我们指定了一个路径来直接指向市场值(参见https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql)
We now return the values returned and filter our UPDATE on those, as we would were we dealing with any other subquery.
现在我们返回返回返回的值，并对这些值进行过滤更新，就像我们处理任何其他子查询一样。

#1