September 19, 2022 at 9:02 am
Hi,
I have a json files inside nvarchar(MAX) column, the structure is like below.
I need a query which would show:
SourceType, JournalID, SourceID, JournalLines.AccountType, JournalLines.JournalLineID, JournalLines.TaxName, JournalLines.TrackingCategories
The number of JournalLines in each journal can be diffrent, normally 5-10 of them.
Any ideas?
Thanks
{
"_Journals": [
{
"SourceType": 18,
"JournalID": "sdfdsf-fdfs-dfdf-dfdfd-dfdsfsfdd",
"SourceID": "sdfsdf-sdfds-sdf-dfd-sdfsdf",
"JournalLines": [
{
"AccountType": 3,
"JournalLineID": "sdfsdfsd-sdfsdf-sdfsd-sdfsdf-sdfsdf",
"TaxName": "Some tax name",
"TrackingCategories": []
},
{
"AccountType": 3,
"JournalLineID": "sdfsdfsdf-sdfsdf-sdf-sdf-sdf",
"TaxName": "text",
"TrackingCategories": []
}
]
},
{
"SourceType": 18,
"JournalID": "sdfsd-sdfd-sdfd-sdf-dfdsfd",
"SourceID": "61da35a7-4f8c-4d3a-9a18-e531734b419c",
"JournalLines": [
{
"AccountType": 3,
"JournalLineID": "sdfdsf-dsfds-sdf-sdfsd-sdfd",
"TaxName": "some text",
"TrackingCategories": []
},
{
"AccountType": 11,
"JournalLineID": "sdfs-sdfsd-sdf-sdfd-sdfds",
"TaxName": "some text",
"TrackingCategories": []
},
{
"AccountType": 2,
"JournalLineID": "sdsfdsfd-sdf-sdf-sdfdsf-sdfsdf",
"TaxName": "some text",
"TrackingCategories": []
}
]
}
]
}
September 19, 2022 at 10:46 am
declare @mytable table(mydata nvarchar(max));
insert into @mytable(mydata)
values(N'
{
"_Journals": [
{
"SourceType": 18,
"JournalID": "sdfdsf-fdfs-dfdf-dfdfd-dfdsfsfdd",
"SourceID": "sdfsdf-sdfds-sdf-dfd-sdfsdf",
"JournalLines": [
{
"AccountType": 3,
"JournalLineID": "sdfsdfsd-sdfsdf-sdfsd-sdfsdf-sdfsdf",
"TaxName": "Some tax name",
"TrackingCategories": []
},
{
"AccountType": 3,
"JournalLineID": "sdfsdfsdf-sdfsdf-sdf-sdf-sdf",
"TaxName": "text",
"TrackingCategories": []
}
]
},
{
"SourceType": 18,
"JournalID": "sdfsd-sdfd-sdfd-sdf-dfdsfd",
"SourceID": "61da35a7-4f8c-4d3a-9a18-e531734b419c",
"JournalLines": [
{
"AccountType": 3,
"JournalLineID": "sdfdsf-dsfds-sdf-sdfsd-sdfd",
"TaxName": "some text",
"TrackingCategories": []
},
{
"AccountType": 11,
"JournalLineID": "sdfs-sdfsd-sdf-sdfd-sdfds",
"TaxName": "some text",
"TrackingCategories": []
},
{
"AccountType": 2,
"JournalLineID": "sdsfdsfd-sdf-sdf-sdfdsf-sdfsdf",
"TaxName": "some text",
"TrackingCategories": []
}
]
}
]
}
');
select j2.SourceType,
j2.JournalID,
j2.SourceID,
j3.AccountType,
j3.JournalLineID,
j3.TaxName,
j3.TrackingCategories
from @mytable t
cross apply openjson(t.mydata, '$._Journals') j1
cross apply openjson(j1.value)
with (SourceType int '$.SourceType',
JournalID varchar(50) '$.JournalID',
SourceID varchar(50) '$.SourceID',
JournalLines nvarchar(max) '$.JournalLines' as json) j2
outer apply openjson(j2.JournalLines)
with (AccountType int '$.AccountType',
JournalLineID varchar(50) '$.JournalLineID',
TaxName varchar(50) '$.TaxName',
TrackingCategories nvarchar(max) '$.TrackingCategories' as json) j3;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537September 19, 2022 at 12:17 pm
Brilliant! Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply