June 14, 2018 at 12:02 am
Hi,
I have a table wrong JSON values, I get the values from API and for some reason they are passing incorrect JSON values/format. I'm going to mimic what I have in table below.
CREATE TABLE TestTable
(
TestTableID int pk
,TestName varchar(20)
,TestAdditionalAttributes varchar(2000)
)
INSERT INTO TestTable
VALUES ('Name1','[{"JOINING FEE":"200"},{"ADDITIONAL FEE":"100"}]'; 'Name2 ','[{"JOINING FEE":"300"},{"ADDITIONAL FEE":"50"}]'; 'Name3','[{"JOINING FEE":"250"},{"ADDITIONAL FEE":"100"}]'
The JSON above is incorrect, the correct format should be '[{"JOINING FEE":"200","ADDITIONAL FEE":"100"}]'. How do I then Update the table to remove the middle curly brackets, as an alternative (I will ask API guy to send the correct values).
June 14, 2018 at 6:55 am
sirkinghorse - Thursday, June 14, 2018 12:02 AMHi,I have a table wrong JSON values, I get the values from API and for some reason they are passing incorrect JSON values/format. I'm going to mimic what I have in table below.
CREATE TABLE TestTable
(
TestTableID int pk
,TestName varchar(20)
,TestAdditionalAttributes varchar(2000)
)INSERT INTO TestTable
VALUES ('Name1','[{"JOINING FEE":"200"},{"ADDITIONAL FEE":"100"}]'; 'Name2 ','[{"JOINING FEE":"300"},{"ADDITIONAL FEE":"50"}]'; 'Name3','[{"JOINING FEE":"250"},{"ADDITIONAL FEE":"100"}]'The JSON above is incorrect, the correct format should be '[{"JOINING FEE":"200","ADDITIONAL FEE":"100"}]'. How do I then Update the table to remove the middle curly brackets, as an alternative (I will ask API guy to send the correct values).
Take a look here and see if that helps at all...
https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2017
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply