SQL Server. JSON. From Json to table

Question

Post reply

SQL Server. JSON. From Json to table

TorNorway

Valued Member

Points: 63
More actions
May 7, 2020 at 3:08 pm

#3749678

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!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

BTylerWhite SSCommitted Points: 1569 More actions · Answer 1

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'
    );

TorNorway Valued Member Points: 63 More actions · Answer 2

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):

SkjermbildeS

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:

SkjermbildeR