Parse and Update Dynamic JSON into Database with esProc

Uncategorized 269 0

Problem source: http://bbs.csdn.net/topics/390611005 .

Below is JSON data (s.json) the system acquires:   

{

    “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;\”>…… </p>”,

                            “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;\”>……</p>”,

                            “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;\”>……</p>”,

                            “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;\”>……</p>”,

                            “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”

}

Based on above JSON data, you need to update database tables with property values of corresponding section. Below is the two tables need updating:

Create table [dbo].[Groups]

(

  [ID] [int] IDENTITY(1,1) NOT NULL,                   –id

  [Groupname] [nvarchar] (50) not null default(”),    –name

  [groupid] [int] not null default(0),

 CONSTRAINT [PK_Groups_id] PRIMARY KEY CLUSTERED

(

    [id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[Services](

  [id]    [int] IDENTITY(1,1) NOT NULL,                             –id

  [Serviceid] [int] not null default(0),           

  [Servicename] [nvarchar] (50) not null default(”),   

  [groupid] [int] not null default(0),                

  [Credit] [decimal] not null default(0.00),

  [Time] [nvarchar] (50) not null default(”),

  [INFO] [nvarchar] (3000) not null default(”),

  [Network] [nvarchar] (100) not null default(‘none’),

  [Mobile] [nvarchar] (100) not null default(‘none’),

  [Provider] [nvarchar] (100) not null default(‘none’),

  [PIN] [nvarchar] (100) not null default(‘none’),

  [KBH] [nvarchar] (100) not null default(‘none’),

  [MEP] [nvarchar] (100) not null default(‘none’),

  [PRD] [nvarchar] (100) not null default(‘none’),

  [Type] [nvarchar] (100) not null default(‘none’),

  [Locks] [nvarchar] (100) not null default(‘none’),

  [Reference] [nvarchar] (100) not null default(‘none’),

  [isstatus] [nvarchar] (1) not null default(‘0’),

  [remark] [nvarchar] (255) not null default(”),

  [Pricingid] [int] not null default(0),

 CONSTRAINT [PK_Services_id] PRIMARY KEY CLUSTERED

(

    [id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] 

The property for SERVICES is groupid, such as 3, 5, 8, 10. The rest of the fields correspond to other properties respectively. That the property names under LIST and SERVICES are not fixed makes the data parsing difficult. Compared with common high-level languages, esProc supports dynamic data structure and set operations and thus can provide easy solution.

esProc script is as follows:

A B C D
1 =file(“E:\\s.json”).read().import@j()
2 =create(Groupname,groupid)
=create(Serviceid,Servicename,groupid,Credit,Time,INFO,Network,

Mobile,Provider,PIN,KBH,MEP,PRD,Type,Locks,Reference)

4 for A1.SUCCESS =A4.LIST.fno()
5 for B4 =eval(“A4.LIST.#”+string(B5))
6 =C5.SERVICES.fno()
7 for C6 =C5.SERVICES.fname(C7)
8 =eval(“C5.SERVICES.#”+string(C7))
9 =A2.record([C5.GROUPNAME,D7])
10 =A3.record([D8.#1,D8.#2,D7,D8.#3,D8.#4,

D8.#5,D8.#6,D8.#7,D8.#8,D8.#9,

D8.#10,D8.#11,D8.#12,D8.#13,

8.#14,D8.#15])

11 =mssql.update(A2,groups,Groupname,groupid;groupid)
12 =mssql.update(A3,services,Serviceid,Servicename,groupid,Credit,Time,INFO,

Network,Mobile,Provider,PIN,KBH,MEP,PRD,Type,Locks,Reference;Serviceid)

A1: Read JSON file into strings and convert them into a cascaded table sequence with rows and columns using import@j(). 

esProc_NoSQL_dynamic_json_2

A2-A3: Create empty table sequences based on the two target tables, in order to store parsing results that will be updated into the database in one go.

A4-B4: Run a loop in A4 and calculate the number of sections under LIST in B4.

B5-C6: Get the content of each section of LIST by loop and calculate the number of sections under SERVICES in C6. 

esProc_NoSQL_dynamic_json_3

C7-D8: Get property names and values from each SERVICES’s section by loop.

esProc_NoSQL_dynamic_json_4

D9-D10: Write parsing results respectively back into the empty table sequences in A2 and A3. 

esProc_NoSQL_dynamic_json_5

A11: Update A2’s data into groups table through the primary key groupid.

esProc_NoSQL_dynamic_json_6

A12: Update A3’s data into services table through the primary key Serviceid.

esProc_NoSQL_dynamic_json_7

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website