Need help read JSON file

Question

Post reply

Need help read JSON file

Adelia

SSC-Addicted

Points: 422

February 24, 2024 at 7:41 am

Go to Answer

#4383967

Hello,

I've JSON file format as following,

{
"acc_div": [
{
"level1_code" : 1104,
"level2_code" : "01",
"gl_code" : "0900",
"description" : "Finance Department - Estate Account     ",
"short_name" : "FINANCE   "
},
{
"level1_code" : 1106,
"level2_code" : "01",
"gl_code" : "0933",
"description" : "Marketing Department - SGRK             ",
"short_name" : "MKTG-KKSR "
},
{
"level1_code" : 1112,
"level2_code" : "01",
"gl_code" : "0931",
"description" : "Marketing Dept. - Ulu Keratong Mill     ",
"short_name" : "MKTG-UKM  "
},
{
"level1_code" : 1113,
"level2_code" : "01",
"gl_code" : "0932",
"description" : "Marketing Dept. - Sg. Ambat Mill        ",
"short_name" : "MKTG-SAM  "
},
{
"level1_code" : 1301,
"level2_code" : "01",
"gl_code" : "0950",
"description" : "Dana Akaun Ladang                       ",
"short_name" : "RTBB A\/C  "
},
{
"level1_code" : 1303,
"level2_code" : "01",
"gl_code" : "0952",
"description" : "Dana Akaun Rezab Tanam Semula           ",
"short_name" : "TTS A\/C   "
},
{
"level1_code" : 1501,
"level2_code" : "01",
"gl_code" : "0962",
"description" : "Makmal RISDA                            ",
"short_name" : "ERAS-LAB  "
},
{
"level1_code" : 30411,
"level2_code" : "01",
"gl_code" : "0901",
"description" : "Ladang RISDA Sungai Ambat               ",
"short_name" : "SG.AMBAT  "
},
{
"level1_code" : 30412,
"level2_code" : "01",
"gl_code" : "0902",
"description" : "Ladang RISDA Sungai Chales              ",
"short_name" : "SG.CHALES "
},
{
"level1_code" : 30414,
"level2_code" : "01",
"gl_code" : "0903",
"description" : "Ladang RISDA Ulu Sedili                 ",
"short_name" : "ULU SEDILI"
},
{
"level1_code" : 30711,
"level2_code" : "01",
"gl_code" : "0101",
"description" : "Sungai Ambat Mill                       ",
"short_name" : "SAM       "
},
{
"level1_code" : 35411,
"level2_code" : "01",
"gl_code" : "0914",
"description" : "Ladang RISDA Tanjumg Genting            ",
"short_name" : "T.GENTING "
},
{
"level1_code" : 50411,
"level2_code" : "01",
"gl_code" : "0911",
"description" : "Bukit Keramat Plantation                ",
"short_name" : "B.KERAMAT "
},
{
"level1_code" : 50421,
"level2_code" : "01",
"gl_code" : "0913",
"description" : "Ladang RISDA Abdullah Kadir             ",
"short_name" : "LAK       "
},
{
"level1_code" : 50431,
"level2_code" : "01",
"gl_code" : "0908",
"description" : "Ladang RISDA Palong 1                   ",
"short_name" : "PALONG 1  "
},
{
"level1_code" : 50432,
"level2_code" : "01",
"gl_code" : "0909",
"description" : "Ladang RISDA Palong 2                   ",
"short_name" : "PALONG 2  "
},
{
"level1_code" : 50441,
"level2_code" : "01",
"gl_code" : "0927",
"description" : "Ladang RISDA Kepis                      ",
"short_name" : "KEPIS     "
},
{
"level1_code" : 55411,
"level2_code" : "01",
"gl_code" : "0912",
"description" : "Ladang RISDA Damak                      ",
"short_name" : "DAMAK     "
},
{
"level1_code" : 55420,
"level2_code" : "01",
"gl_code" : "0999",
"description" : "Kilang Ulu Keratong                     ",
"short_name" : "UKM       "
},
{
"level1_code" : 55421,
"level2_code" : "01",
"gl_code" : "0904",
"description" : "Ladang RISDA Keratong 1                 ",
"short_name" : "U.K.1     "
},
{
"level1_code" : 55422,
"level2_code" : "01",
"gl_code" : "0905",
"description" : "Ladang RISDA Keratong 2                 ",
"short_name" : "U.K.2     "
},
{
"level1_code" : 55423,
"level2_code" : "01",
"gl_code" : "0906",
"description" : "Ladang RISDA Keratong 3                 ",
"short_name" : "U.K.3     "
},
{
"level1_code" : 55424,
"level2_code" : "01",
"gl_code" : "0907",
"description" : "Ladang RISDA Ulu Keratong 4             ",
"short_name" : "U.K.4     "
},
{
"level1_code" : 55431,
"level2_code" : "01",
"gl_code" : "0910",
"description" : "Ladang RISDA Bera                       ",
"short_name" : "BERA      "
},
{
"level1_code" : 65411,
"level2_code" : "01",
"gl_code" : "0915",
"description" : "Ladang RISDA Bukit Kota 1               ",
"short_name" : "BK.KOTA 1 "
},
{
"level1_code" : 65412,
"level2_code" : "01",
"gl_code" : "0916",
"description" : "Ladang RISDA Bukit Kota 2               ",
"short_name" : "BK.KOTA 2 "
},
{
"level1_code" : 65413,
"level2_code" : "01",
"gl_code" : "0917",
"description" : "Ladang RISDA Bukit Kota 3               ",
"short_name" : "BK.KOTA 3 "
},
{
"level1_code" : 65421,
"level2_code" : "01",
"gl_code" : "0918",
"description" : "Ladang RISDA Serigala                   ",
"short_name" : "SERIGALA  "
},
{
"level1_code" : 65431,
"level2_code" : "01",
"gl_code" : "0919",
"description" : "Ladang RISDA Ulu Slim                   ",
"short_name" : "ULU SLIM  "
},
{
"level1_code" : 80411,
"level2_code" : "01",
"gl_code" : "0926",
"description" : "Ladang RISDA Gerdong                    ",
"short_name" : "GERDONG   "
},
{
"level1_code" : 80431,
"level2_code" : "01",
"gl_code" : "0920",
"description" : "Ladang RISDA Durian Mas 1               ",
"short_name" : "D.MAS 1   "
},
{
"level1_code" : 80432,
"level2_code" : "01",
"gl_code" : "0921",
"description" : "Ladang RISDA Durian Mas 2               ",
"short_name" : "D.MAS 2   "
},
{
"level1_code" : 80433,
"level2_code" : "01",
"gl_code" : "0922",
"description" : "Ladang RISDA Terengganu Tengah 3        ",
"short_name" : "T.T.3     "
},
{
"level1_code" : 80434,
"level2_code" : "01",
"gl_code" : "0923",
"description" : "Ladang RISDA Durian Mas 3               ",
"short_name" : "D.MAS 3   "
},
{
"level1_code" : 80435,
"level2_code" : "01",
"gl_code" : "0924",
"description" : "Ladang RISDA Durian Mas 4               ",
"short_name" : "D.MAS 4   "
},
{
"level1_code" : 80436,
"level2_code" : "01",
"gl_code" : "0925",
"description" : "Ladang RISDA Cerul                      ",
"short_name" : "CERUL     "
},
{
"level1_code" : 80437,
"level2_code" : "01",
"gl_code" : "0928",
"description" : "Ladang RISDA Ceneh 1                    ",
"short_name" : "CENEH 1   "
},
{
"level1_code" : 80438,
"level2_code" : "01",
"gl_code" : "0929",
"description" : "Ladang RISDA Ceneh 2                    ",
"short_name" : "CENEH 2   "
},
{
"level1_code" : 90301,
"level2_code" : "01",
"gl_code" : "0972",
"description" : "Kilang Ulu Keratong                     ",
"short_name" : "KUK       "
},
{
"level1_code" : 90302,
"level2_code" : "01",
"gl_code" : "0971",
"description" : "Kilang Sg. Ambat                        ",
"short_name" : "KSA       "
},
{
"level1_code" : 90303,
"level2_code" : "01",
"gl_code" : "0973",
"description" : "Kilang Durian Mas                       ",
"short_name" : "KDM       "
}
]}

This is my Query,

Declare @JSON varchar(max);

SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\acc_div_202402041349.json', SINGLE_CLOB) import

SELECT *
FROM OPENJSON (@JSON)
WITH  (
[level1_code] [int] ,
[level2_code] [varchar](20) ,
[gl_code] [varchar](40) ,
[description] [varchar](40) ,
[short_name] [varchar](10) 
   
   )
   ;

Unfortunately, I got this

24022024-001

I suspect because of this,

24022024-002

How to modify my Query ?

Please help

Attachments:

You must be logged in to view attached files.

Ken McKelvey

SSCoach

Points: 18842
More actions
February 24, 2024 at 2:41 pm
Answer
#4384137
```
FROM OPENJSON (@JSON, '$.acc_div')
```

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

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

Adelia SSC-Addicted Points: 422 More actions · Answer 1

Ken McKelvey wrote:

FROM OPENJSON (@JSON, '$.acc_div')

Thanks a lot