SQLTeam.com | Weblogs | Forums

Comparing JSON data in two rows for every set of ID

Hi All,

Hope you are doing well!..I have a billing file data in which I am trying to compare JSON data for each pair of Billid and Ctextid ..Each pair of Billid and Ctextid have two rows of JSON data..I am trying to check if the two rows of JSON are the same or whether there are any changes made... Can you please help me here!..Please find below the sample Input data and Output data..

Input table
create table ##input1
(Billid int,
Ctextid int,
info JSON,
user varchar(30)
)
insert into ##input1 values

"('2132','91156','[
{
""description"": ""fabula "",
""dCode"": ""8901"",
""CodeId"": ""90001"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""EXper "",
""dCode"": ""9034"",
""CodeId"": ""88343"",
""messages"": [
""""
],
""Number"": 2
}
]','amt1'),"
"('2132','91156','[
{
""description"": ""fabula "",
""dCode"": ""8901"",
""CodeId"": ""90001"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""EXper "",
""dCode"": ""9034"",
""CodeId"": ""88343"",
""messages"": [
""""
],
""Number"": 2
}
]','all1'),"
"('5678','99344','[
{
""description"": ""TORYA "",
""dCode"": ""99002"",
""CodeId"": ""988332"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""triact "",
""dCode"": ""90774"",
""CodeId"": ""7800034"",
""messages"": [
""""
],
""Number"": 2
}
]','jk1'),"
"('5678','99344','[
{
""description"": ""TORYA "",
""dCode"": ""99002"",
""CodeId"": ""988332"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""triact "",
""dCode"": ""90974"",
""CodeId"": ""78034"",
""messages"": [
""""
],
""Number"": 2
}
]','jk2'),"
Ouput Table
create table #output
(Billid int,
Ctextid int,
Infochange varchar(20)
)

Insert into #output values
('2132','91156','No'),
('5678','99344','Yes')

Thanks,
Arun

Could you please test your sample data and re-post

@ [yosiasz]: Apologies for the delay!...Yes I will test the data and repost...