CODEWITHSUNDEEP
jsonsql-serverjson-query
Bart
Bart

Reputation: 231

SQL Server: How to update all the keys inside an json array

I have a query like (simplified):

SELECT 
    JSON_QUERY(r.SerializedData, '$.Values') AS [Values] 
FROM 
    <TABLE> r 
WHERE ...

The result is like this:

{ "201902":121, "201903":134, "201904":513 }

How can I change this in SQL to:

{ "2019-02-01":121, "2019-03-01":134, "2019-04-01":513 }

Meaning after the year (4 digits) add an '-' and end the key with '-01'.

Upvotes: 0

Views: 728

Answers (1)

lptr
lptr

Reputation: 6788

For SQL2017 and later, you could use the example of renaming a key (which might be a bit cumbersome and might not work properly in case of duplicate keys)

declare @j1 nvarchar(max);
select @j1 = N'{"201902":121, "201902":145, "201903":134, "201904":513}';

select @j1 as before;

select @j1 = JSON_MODIFY(JSON_MODIFY(@j1, concat('$."', left(a.[key], 4), '-', right(a.[key], 2), '-01"'), CAST(a.value AS int)), concat('$."', a.[key], '"'), null)
from openjson(@j1) as a;

select @j1 as after, isjson(@j1);

Or handle json as a string with a simple REPLACE()

declare @j2 nvarchar(max);
select @j2 = N'{"201902":121, "201902":145, "201903":134, "201904":513}';

select @j2 as before;

select @j2 = replace(@j2, a.[key], concat(left(a.[key], 4), '-', right(a.[key], 2), '-', '01'))
--if keys appear as values...
--replace(@j2, concat('"', a.[key], '"'), concat('"', stuff(a.[key],5, 0, '-'), '-01"'))
from openjson(@j2) as a;

select @j2 as after, isjson(@j2);

Upvotes: 1

Related Questions