"【Question】 I need to pull json data from a web api to store it in my database. But I know nothin .."

blackduckie RaqForum 28 No.
648 View • 5 Years ago

Writing Dynamic JSON Data to Database

json&xml(9)

【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”:“<spanstyle=\“color:#ff0000;\”data-mce-mark=\“1\”>SERVICIO INSTANTE DE 0-10 MINS PARA NOKIAS MOVISTARï¼ˆNodct3/4ï¼‰

\r\n<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\nSERIA ASHA 100, 101, 311, 500 y todos los de estas series

\r\n 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:

undefined

Result of executing A3:

undefined

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 👉 https://www.scudata.com

SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL

SPL Learning Material 👉 https://c.scudata.com

SPL Source Code and Package 👉 https://github.com/SPLWare/esProc

Discord 👉 https://discord.gg/cFTcUNs7

Youtube 👉 https://www.youtube.com/@esProc_SPL

json&xml(9)

Application

blackduckie • 648 View • 5 Years ago