Json writes dynamic columns to the database

[questions]

Recently, a website needs to obtain json data from the api of another website and store it in its own database. But I know nothing about json operation, so please consult your great God. No more nonsense. The code is as follows
json file content I have cleaned up most of the data with the same structure:
(mainly imei's Service)   I put it into two tables in the database, groups and 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;\"><span style=\"color: #ff0000;\" data-mce-mark=\"1\">SERVICIO INSTANTE DE 0-10 MINS PARA NOKIAS MOVISTAR（Nodct3/4）

\r\n<pstyle=\"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": "<p style=\"text-align: center;\"><span style=\"font-size: small; color: #ff0000;\">ALL NOKIA MOVISTAR SPAIN 20 DIGITS ,<strong style=\"color: #ff0000; font-size: small;\">NOT SUPPORTED LUMIA 820 

\r\n<p style=\"text-align: center;\"><strong style=\"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<p style=\"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": "<p style=\"text-align: center;\"><span style=\"font-size: medium; color: #ff0000;\">Nokia lumia, nokia 100, 610 y nokias 20 digitos no soportados!

\r\n<p style=\"text-align: center;\"><span style=\"color: #ff0000; font-size: small;\"> Nokia vodafone instante en horario de la tienda



\r\n<p style=\"text-align: center;\"><span style=\"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": "<p style=\"text-align: center;\"><span style=\"color: #ff0000; font-size: medium;\">VODAFONE PHONE ,NO NOKIA Y WINDOWN PHONE,NO IPHONE 

\r\n<p style=\"text-align: center;\"><span style=\"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"

}

The following is the structure of the two tables in the sql database:
Here, all the required fields are listed:   The name of the group is unique. Then the two tables are associated according to the 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),                                –Required points

[Time] [nvarchar] (50) notnulldefault(''),                                –Time required

[INFO] [nvarchar] (3000) notnulldefault(''),                            –Information description 

[Network] [nvarchar] (100) notnulldefault('none'),                        –Network requirements

[Mobile] [nvarchar] (100) notnulldefault('none'),                        –Mobile demand

[Provider] [nvarchar] (100) notnulldefault('none'),                        –Supplier requirements

[PIN] [nvarchar] (100) notnulldefault('none'),                            –mobile phone PIN Code requirements

[KBH] [nvarchar] (100) notnulldefault('none'),                            –KBH demand

[MEP] [nvarchar] (100) notnulldefault('none'),                            –MEP demand

[PRD] [nvarchar] (100) notnulldefault('none'),                            –PRD demand

[Type] [nvarchar] (100) notnulldefault('none'),                            –Type requirements

[Locks] [nvarchar] (100) notnulldefault('none'),                        –Locked

[Reference] [nvarchar] (100) notnulldefault('none'),                    –Reference requirements

[isstatus] [nvarchar] (1) notnulldefault('0'),                            –state 0 available 1 Not available

[remark] [nvarchar] (255) notnulldefault(''),                            –remarks

[Pricingid] [int] notnulldefault(0),                                    –Price strategy

CONSTRAINT[PK_Services_id] PRIMARYKEYCLUSTERED 

(

[id] ASC

)WITH(IGNORE_DUP_KEY = OFF) ON[PRIMARY]

) ON[PRIMARY]

  Because there are other json files like this, I only need to have a conversion example of this method. The best thing is to be able to successfully convert and store the above code in the database. It is convenient for me to operate other json by reference. There were many other json files before, but I still don't understand. Moreover, the format of the above json is different from that of others on Baidu.

[answer]

The JSON string here contains multiple layers and many layers are dynamic (for example, the number and name of nodes under LIST and SERVICES are not fixed), which brings great difficulty to the resolution. In addition, the attribute name part also contains spaces (such as movstar span) and dots (such as requirements. Network), which also greatly increases the difficulty of resolution. It is too difficult to write in JAVA or C # too...

SPL provides JSON parsing function, and can also write the parsing results into the database to complete the requirements in the title. The script can be written as follows:

ABCDE
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)
5for A2.SUCCESS=A5.LIST.fno()
6for 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)

A3 operation results:

A4 operation results:

A1:   Read json string

A2:   Generate sequence table

A3: create an ordinal table with column names

A4: create an ordered table with column names

A5~A11: read json data circularly and fill in A3 and A4 sequence tables

A12: connect to database

A13: update data to groups table;

A14: update data to services table

Keywords: Database Big Data JSON

Added by chaking on Fri, 08 Oct 2021 07:37:39 +0300

Popular Keywords

©2024 Programming VIP