SQL Server 2016 - How to select integer array from JSON

Question

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

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

How to select the market IDs correctly?

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

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

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

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

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

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:

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

JohnLBevan · Accepted Answer · 2018-03-20 01:29:33Z

12

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
Map the first field returned to the Comments column with type 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://learn.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)
Use a cross apply to apply the OPENJSON function to the Markets column so we can fetch values from that property.
Finally use the WITH statement to map the name Market to the returned value, and assign it a data type of 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
However this time we specify a path to point at the Markets value directly (see https://learn.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.

edited Mar 20, 2018 at 1:29

answered Mar 20, 2018 at 1:14

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FisNaN Over a year ago

Wow, I see what's going on. I was missing the AS JSON part, so I got null from Markets. Thank you

JohnLBevan Over a year ago

No worries. ps. If you wanted the first query (i.e. where you're getting other columns too) you may want to switch the cross apply for an outer apply depending on how you'd want things to behave when markets is an empty array. Don't think that applies in your current use case though.

Collectives™ on Stack Overflow

SQL Server 2016 - How to select integer array from JSON

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related