json string to DB File Field Mapping

[rkanemeier]

Is there a way to take a json string that contains field names and values and map the to an appropriate file (dynamically)?

Here is a sample DB file layout:

A            PARTNO    R               REFFLD(PARTNO)       
A            PARTDESC  R               REFFLD(DESC)       
A            PARTTYPE  R               REFFLD(TYPE)       
A            CATEGORY  R               REFFLD(CATEGORY)       
A            COST      R               REFFLD(COST)       
A            QTY       R               REFFLD(QUANTITY)

Here is a sample json string:

[
{"PARTNO":"ABC123","PARTDESC":"AIR FILTERXXX","PARTTYPE":"X"},
"PARTNO":"ABC999","PARTDESC":"AIR FILTERYYY","CATEGORY":"XXXXX"}
]

Is there a way to map those values to a record? I tried using vvin_json, but it seems you need to have a json string that matches an exact record layout for it to work properly.

[robert.swanson]

vvIn_json should do exactly that. It shouldn't matter if you don't have all the fields in the record format or data structure. What's it doing when you try it?

[rkanemeier]

Rob. Thanks for your response. I must have corrected something late yesterday that resolved the problem. The behavior I was seeing from the vvin_json api was incorrect mapping. It would map values into the wrong data structure fields. I thought it had something to do with the 2 json records specifying different fields. I usually see this type of behavior when I get an IBM API parameter definition wrong (ie. QMHRCVPM), which is a possibility as I changed that API in my program.

I'm not sure if I need to open up a new thread, but is there a way to manually re-parse the consumed json string that vvin_json would use? Here's why I ask this question.

1. Parse json string into a data structure array
2. Check if record exists in DB file
3. if record exists, populate that occurrence of the array with the remaining DB file values that were not supplied by the json string (do not update existing record yet).

One way I thought I could do this is to use a dynamic sql statement to update the data structure. However, SQLRPGLE will not allow you to build / prepare a dyamic SQL statement with program defined variables (it would have to be static). So in step 2 above, I know I have a matching record in my DB file, but now how do i retrieve all "other" values and store them in an array?

[robert.swanson]

That's a pretty specialized need in #3, certainly nothing in VVSRVPGM would handle that "out of the box"... I think you're going to have to write your own RPG procedure to accomplish that.

[rkanemeier]

Thanks for confirming my suspicion Rob. I'm going to have to build this ridiculous routine that does a field by field mapping. One of the files I'm building a backend maintenance program for has 150 fields. lol.