Writing Dynamic JSON Data to Database
【Question】
I need to pull json data from a web api to store it in my database. But I know nothing about handling json data. Can anyone help me do this?
Here’s my json data. I’ve already remove a part of same-structure data. The rest of the data mainly includes IMEI Service List, which I put it in two database tables: groups & Services.
{
“SUCCESS”:[
{
“MESSAGE”: “IMEI Service List”,
“LIST”:{
“MOVISTAR SPAIN”:{
“GROUPNAME”:“MOVISTAR SPAIN”,
“SERVICES”:{
“3”:{
“SERVICEID”:32,
“SERVICENAME”:“MOVISTAR NOKIA INSTANTE”,
“CREDIT”:4,
“TIME”:“1-30 Minutes”,
“INFO”:“<p style=\“text-align:center;\”><spanstyle=\“color:#ff0000;\”data-mce-mark=\“1\”>SERVICIO INSTANTE DE 0-10 MINS PARA NOKIAS MOVISTAR（Nodct3/4）
\r\n<p style=\“text-align:center;\”><spanstyle=\“color:#ff0000;\” data-mce-mark=\“1\”>CON GUIDADO SERIA C ，mayoriason20digitos
\r\n<pstyle=\“text-align:center;\”>å�心部分机型是20ä½�:å<spanstyle=\“font-size:medium;\”> C系列(c2c5)，Nokia7230
\r\n<p style=\“text-align:center;\”><span style=\“color:#ff0000;\” data-mce-mark=\“1\”>SERIA ASHA 100, 101, 311, 500 y todos los de estas series
\r\n<p style=\“text-align: center;\”><span style=\“color: #ff0000;\”data-mce-mark=\“1\”> Y WINDOWS PHONE Lumia
”,
“Requires.Network”: “None”,
“Requires.Mobile”: “None”,
“Requires.Provider”: “None”,
“Requires.PIN”: “None”,
“Requires.KBH”: “None”,
“Requires.MEP”: “None”,
“Requires.PRD”: “None”,
“Requires.Type”: “None”,
“Requires.Locks”: “None”,
“Requires.Reference”: “None”
},
“8”: {
“SERVICEID”: 77,
“SERVICENAME”: “MOVISTAR NOKIA 20 NCK”,
“CREDIT”: 12,
“TIME”: “1-30 Minutes”,
“INFO”:“<pstyle=\“text-align:center;\”><spanstyle=\“font-size:small;color:#ff0000;\”>ALL NOKIA MOVISTAR SPAIN 20 DIGITS,<strongstyle=\“color:#ff0000;font-size: small;\”>NOT SUPPORTED LUMIA 820
\r\n<pstyle=\“text-align:center;\”><strongstyle=\“color:#ff0000;font-size:small;\”>Para Confirma si es 20 nck o no,check info de movil con programa infinity ,descarga desde pagina aportado
\r\n<pstyle=\“text-align:center;\”>
”,
“Requires.Network”: “None”,
“Requires.Mobile”: “None”,
“Requires.Provider”: “None”,
“Requires.PIN”: “None”,
“Requires.KBH”: “None”,
“Requires.MEP”: “None”,
“Requires.PRD”: “None”,
“Requires.Type”: “None”,
“Requires.Locks”: “None”,
“Requires.Reference”: “None”
}
}
},
“VODAFONE SPAIN”:{
“GROUPNAME”: “VODAFONE SPAIN”,
“SERVICES”:{
“5”:{
“SERVICEID”:50,
“SERVICENAME”:“VODAFONE NOKIA BB5 SL3”,
“CREDIT”:5,
“TIME”:“1-60 Minutes”,
“INFO”:“<pstyle=\“text-align:center;\”><spanstyle=\“font-size:medium;color:#ff0000;\”>Nokia lumia, nokia 100, 610 y nokias 20 digitos no soportados!
\r\n<pstyle=\“text-align:center;\”><spanstyle=\“color:#ff0000;font-size:small;\”>Nokia Vodafone instante en horario de la tienda
\r\n<pstyle=\“text-align:center;\”><spanstyle=\“color:#ff0000;font-size:small;\”>20ä½�ç �的手机和LUMIAä¸�支æŒ�
\r\n<pstyle=\“text-align:center;\”><spanstyle=\“color:#ff0000;font-size:small;\”>
”,
“Requires.Network”:“None”,
“Requires.Mobile”:“None”,
“Requires.Provider”:“None”,
“Requires.PIN”:“None”,
“Requires.KBH”:“None”,
“Requires.MEP”:“None”,
“Requires.PRD”:“None”,
“Requires.Type”:“None”,
“Requires.Locks”:“None”,
“Requires.Reference”:“None”
},
“10”:{
“SERVICEID”:95,
“SERVICENAME”:“VODAFONE SONY&SONY ERIC(RAPIDO)”,
“CREDIT”:16,
“TIME”:“1-24 Hours”,
“INFO”:“<pstyle=\“text-align:center;\”><spanstyle=\“color:#ff0000;font-size:medium;\”>VODAFONE PHONE,NO NOKIA Y WINDOWN PHONE,NO IPHONEÂ
\r\n<pstyle=\“text-align:center;\”><spanstyle=\“color:#ff0000;font-size:medium;\”>SAMSUNG CODIGO NO LLEVA DEFREEZE
”,
“Requires.Network”:“None”,
“Requires.Mobile”:“None”,
“Requires.Provider”:“None”,
“Requires.PIN”:“None”,
“Requires.KBH”:“None”,
“Requires.MEP”:“None”,
“Requires.PRD”:“None”,
“Requires.Type”:“None”,
“Requires.Locks”:“None”,
“Requires.Reference”:“None”
}
}
}
}
}
],
“apiversion”:“2.0.0”
}
Below are structures of the two tables in SQL database. All fields need to be listed. The name of group is unique. The two tables are associated by groupid.
Createtable[dbo].[Groups]
(
[ID] [int] IDENTITY(1,1) NOTNULL, –id
[Groupname] [nvarchar] (50) notnulldefault(''), –name
CONSTRAINT[PK_Groups_id] PRIMARYKEYCLUSTERED
(
[id] ASC
)WITH(IGNORE_DUP_KEY=OFF) ON[PRIMARY]
)ON[PRIMARY]
CREATETABLE[dbo].[Services](
[id] [int] IDENTITY(1,1) NOTNULL, –id
[Serviceid] [int] notnulldefault(0), –service id
[Servicename] [nvarchar] (50) notnulldefault(''), –service name
[groupid] [int] notnulldefault(0), –group id
[Credit] [decimal] notnulldefault(0.00), –credit needed
[Time] [nvarchar] (50) notnulldefault(''), –time needed
[INFO] [nvarchar] (3000) notnulldefault(''), –information
[Network] [nvarchar] (100) notnulldefault(‘none’), –network
[Mobile] [nvarchar] (100) notnulldefault(‘none’), –mobile phone
[Provider] [nvarchar] (100) notnulldefault(‘none’), –service provider
[PIN] [nvarchar] (100) notnulldefault(‘none’), –PIN code
[KBH] [nvarchar] (100) notnulldefault(‘none’), –KBH
[MEP] [nvarchar] (100) notnulldefault(‘none’), –MEP
[PRD] [nvarchar] (100) notnulldefault(‘none’), –PRD
[Type] [nvarchar] (100) notnulldefault(‘none’), –type
[Locks] [nvarchar] (100) notnulldefault(‘none’), –lock or not
[Reference] [nvarchar] (100) notnulldefault(‘none’), –reference
[isstatus] [nvarchar] (1) notnulldefault(‘0’), –status 0 available & status 1 unavailable
[remark] [nvarchar] (255) notnulldefault(''), –remark
[Pricingid] [int] notnulldefault(0), –pricing
CONSTRAINT[PK_Services_id] PRIMARYKEYCLUSTERED
(
[id] ASC
)WITH(IGNORE_DUP_KEY=OFF) ON[PRIMARY]
)ON[PRIMARY]
There are other json files, i just need a code example of exporting a file to the database. There are many examples on Google, but I can’t completely understand and their json formats are different. Is there any help? Thanks1
【Answer】
Your JSON string has multiple levels and many levels of data are dynamic (For instance, the number of nodes under LIST and SERVICES and their names are unfixed). This complicates the parsing process. Moreover, the attribute names contain spaces (like MOVISTAR SPAIN) and dots (like Requires.Network). So it’s hard to code the problem in Java or C#.
esProc SPL provides functions to parse JSON data and export the result to database. You can use the following SPL script to solve the problem:
| A |
B |
C |
D |
E |
|
| 1 |
=file(“d:\\s.json”).read() |
||||
| 2 |
=json(A1) |
||||
| 3 |
=create(Groupname,groupid) |
||||
| 4 |
=create(Serviceid,Servicename,groupid,Credit,Time,INFO,Network,Mobile,Provider,PIN,KBH,MEP,PRD,Type,Locks,Reference) |
||||
| 5 |
for A2.SUCCESS |
=A5.LIST.fno() |
|||
| 6 |
for B5 |
=A5.LIST.field(B6) |
|||
| 7 |
=C6.SERVICES.fno() |
||||
| 8 |
for C7 |
=C6.SERVICES.fname(C8) |
|||
| 9 |
=C6.SERVICES.field(C8) |
||||
| 10 |
=A3.record([C6.GROUPNAME,D8]) |
||||
| 11 |
=A4.record([D9.#1,D9.#2,D8,D9.#3,D9.#4,D9.#5,D9.#6,D9.#7,D9.#8,D9.#9,D9.#10,D9.#11,D9.#12,D9.#13,D9.#14,D9.#15]) |
||||
| 12 |
=connect(“demo”) |
||||
| 13 |
=A12.update(A3,groups,Groupname,groupid;groupid) |
||||
| 14 |
=A12.update(A4,services,Serviceid,Servicename,groupid,Credit,Time,INFO,Network,Mobile,Provider,PIN,KBH,MEP,PRD,Type,Locks,Reference;Serviceid) |
||||
Result of executing A3:
Result of executing A3:
A1: Read in the json string;
A2: Parse the json string into a table sequence;
A3: Create a table sequence consisting of two fields;
A4: Create a table sequence consisting of multiple fields;
A5~A11: Read in the json string by loop to populate it into A3 and A4’s table sequences;
A12: Connect to the database;
A13: Update data into groups table;
A14: Update data into services table.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
