Need help read JSON file
-
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
I suspect because of this,
How to modify my Query ?
Please help
-
FROM OPENJSON (@JSON, '$.acc_div')
- Ken McKelvey wrote:
FROM OPENJSON (@JSON, '$.acc_div')
Thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply