Replacing JSON Formatted String in SQL

Question

I have a something like this in my table column:

{"InputDirection":0,"Mask":"AA","FormatString":null,"AutoCompleteValue":null,
 "Filtered":"0123456789","AutoComplete":false,"ReadOnly":true}

What I want to do is to change A to N in "Mask":"AA" and remove "Filtered":"0123456789" if they exist. Mask could be in different forms like A9A, 'AAAA`, etc.

If it was in C# I could do it by myself by parsing it to JSON, etc but I need to do it within SQL.

I've found this article which shows how to parse JSON to Table. This gave me an idea that I can parse each field to temp table and make the changes on that and convert it back to JSON so update the actual field where I take this JSON field from. However, this looks like a cumbersome process for both me and the server.

Any better ideas?

"this looks like a cumbersome process" --- that's what happens when you want to store structured data in hardly manageable form. — zerkms
– zerkms, Commented May 21, 2013 at 21:41
Since you can do it in C#, why not write that and expose it as a SQL function? — araqnid
– araqnid, Commented May 21, 2013 at 22:16
@Tarik you can write a method in C# (can be any CLR language) and link the assembly into SQL Server: I've done this for being able to gzip text into blobs, although it was a while back so I'm hazy on the details. msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx — araqnid
– araqnid, Commented May 21, 2013 at 23:07

Ravi Singh · Accepted Answer · 2013-05-22 06:20:30Z

3

You can use this LINK .

And then use the following code

select * into #demo from
(Select * from parseJSON('{"InputDirection":0,"Mask":"AA","FormatString":null,"AutoCompleteValue":null,
 "Filtered":"0123456789","AutoComplete":false,"ReadOnly":true}

')) a

select * from #demo

--- CHANGE THE DATA HERE AS REQUIRED


DECLARE @MyHierarchy JSONHierarchy;
INSERT INTO @myHierarchy
select * from #demo;


-- USE THIS VALUE AND UPDATE YOUR JSON COLUMN
SELECT dbo.ToJSON(@MyHierarchy)


drop table #demo

answered May 22, 2013 at 6:20

Ravi Singh

2,08013 silver badges29 bronze badges

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

2 Comments

Ravi Singh Over a year ago

@Tarik : Does this help or I gave solution in wrong direction ?

geekinit Over a year ago

This is really cool but the parseJSON function throws an error, "Invalid length parameter passed to the LEFT or SUBSTRING function." when it encounters on empty array in the hierarchy (for example: [ ]).

Dario Cage · Accepted Answer · 2013-05-22 08:45:57Z

0

I may be getting something wrong here but why can’t you simply use REPLACE to update what’s needed and LIKE to identify JSON strings that should be updated?

update table_T
set json_string = REPLACE(json_string, '"Filtered":"0123456789",', '')
where json_string like '%"Mask":"AA"%'

Not sure I understand why do you need to parse it….

answered May 22, 2013 at 8:45

Dario Cage

1941 silver badge4 bronze badges

3 Comments

Ravi Singh Over a year ago

We also need to change 'A' to 'N' only for Mask's value. Parsing it will allow any other change also easily.

Tarik Over a year ago

Yes, we also need to change 'A' to 'N' my precioussss...!

dmajka Over a year ago

[42883] ERROR: function replace(json, unknown, unknown) does not exist. No function matches the given name and argument types. You might need to add explicit type casts. - PostgreSQL

dmajka · Accepted Answer · 2023-10-12 10:09:30Z

-1

I finally found how to do it on postgresql:

UPDATE table_name
SET column_name = replace(column_name::TEXT, '"name:"', '"my-other-name:"')::json
WHERE id = 1;

answered Oct 12, 2023 at 10:09

dmajka

1371 silver badge4 bronze badges

Collectives™ on Stack Overflow

Replacing JSON Formatted String in SQL

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related