May 7, 2020 at 3:08 pm
Hi!
I've got a table where the content of a JSON-file is put into a column. I want to "unpack" that JSON into a structured table. I've never worked with JSON before. Below I 've posted an example of how the data in the "Json-column" looks like. (There are more lines, but I deleted them for the sake of simplicity).
[
{
"Income": "1442000000",
"Costs": "-154000000",
"Result": "1288000000",
"EndOfYear": "20181231",
"Organisation": "9914864445"
},
{
"Income": "546000000",
"Costs": "-144000000",
"Result": "402000000",
"EndOfYear": "20171231",
"Organisation": "9914864445"
}
]
Is it possible to query this and get a table like the one below?
1 record pr JSON-file/meassage. Same kind of attributes. The key is organisation (number). Figures for 3 years (EndOfYear)
I sincerely hope someone can help me!
Thanks in advance!
May 7, 2020 at 3:40 pm
Please take a look at the following article: Parse and Transform JSON Data with OPENJSON (SQL Server)
If you're attempting this in SSMS, please try the following.
DECLARE @JSON nvarchar (MAX);
SET @JSON = N'
[
{
"Income": "1442000000",
"Costs": "-154000000",
"Result": "1288000000",
"EndOfYear": "20181231",
"Organisation": "9914864445"
},
{
"Income": "546000000",
"Costs": "-144000000",
"Result": "402000000",
"EndOfYear": "20171231",
"Organisation": "9914864445"
}
]
';
SELECT Income,
Costs,
Result,
EndOfYear,
Organisation
FROM
OPENJSON(@JSON)
WITH
(
Income bigint '$.Income',
Costs bigint '$.Costs',
Result bigint '$.Result',
EndOfYear date '$.EndOfYear',
Organisation bigint '$.Organisation'
);
May 8, 2020 at 2:12 pm
Thank you very much! You helped me on the right track in order to "crack the code"
Parts of the content in the column looks like this (with Norwegian field names):
I did like this (English column names instead of Norwegian ones):
DECLARE @JSON nvarchar (4000);
select
@json = t.[AccountingJSON]
from (
SELECT [AccountingJSON]
FROM [StageRobot].[Accounting]
where id = 19) t
;
SELECT
Income,
Costs,
Result,
EndOfYear,
Organisation
[Result Before Taxes] as ResultBeforeTaxes -- For later use in a table
FROM
OPENJSON(@JSON)
WITH
(
Income Bigint '$.Income',
Costs Bigint '$.Costs',
Result Bigint '$.Result',
EndOfYear Bigint '$.EndOfYear',
Organisation Bigint '$.Organisation',
"Result Before Taxes" Bigint '$."Result Before Taxes"', -- Apostrophes did the trick
);
And I managed to get what I'm after. But: Some fields in the JSON-data has white spaces... Eg. "Result before taxes": "1276000000",
See last line in the select statement above. I tried many things, but when I use apostrophe " before and after the field name it worked
Result:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy