SQL Server. JSON. From Json to table

Question

Post reply

SQL Server. JSON. From Json to table

TorNorway

SSC Enthusiast

Points: 113
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 3 (of 3 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 SSC Enthusiast Points: 113 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

SQL Server. JSON. From Json to table

Cookies on SQLServerCentral