CODEWITHSUNDEEP
jsonsql-servert-sql
FisNaN
FisNaN

Reputation: 2875

SQL Server 2016 - How to select integer array from JSON

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

enter image description here

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)

Upvotes: 5

Views: 8270

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24470

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')
);

Upvotes: 12

Related Questions