Get count of multiple Ints in array in multiple JSON strings in MS SQL Server

Question

I have been searching this topic and I am finding examples that deal with strings but not so much about arrays. I start out with a JSON string stored in a single column, looking like this...

I then can use this query to extract just the Ids portion of each record...

SELECT Id, Message,
    JSON_QUERY(Message, 'strict$.Ids') AS Ids
FROM ActionQueue
WHERE Status = 1
    AND (Action = 'Approve' OR Action = 'Reject')

I need to now combine those arrays (not sure if they are strings that look like arrays?) into a single array.

I need to use that list of Ids for passing to a stored procedure as well as getting a count of the Ids.

lptr · Accepted Answer · 2020-07-31 16:38:06Z

1

declare @t table(thejsoncolumn nvarchar(max));

insert into @t(thejsoncolumn)
values(N'{"Ids":[1, 2, 3, 4, 5]}'), (N'{"Ids":[7]}'), (N'{"Ids":[6, 9, 10, 11]}');

select stuff(
(select concat(',', value)
--string_agg(value, ',') within group (order by cast(value as int)) AS thelist
from
(
select distinct j.value
from @t as t
cross apply openjson(t.thejsoncolumn, '$.Ids') as j
) as src
order by cast(value as int)
for xml path('') --..numbers only
), 1, 1, '');

edited Jul 31, 2020 at 16:38

answered Jul 31, 2020 at 16:20

lptr

6,8582 gold badges10 silver badges16 bronze badges

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

1 Comment

Connie DeCinko Over a year ago

Msg 195, Level 15, State 10, Line 6 'string_agg' is not a recognized built-in function name. Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'as'.

Collectives™ on Stack Overflow

Get count of multiple Ints in array in multiple JSON strings in MS SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related