Is it possible to store a json file into master

Latika6

New Member

Is it possible to store a json file into master-child temp-tables. I wish to store the comma separated list in the child table as separate records​

{
"dsPrRateValueUpload":{
"ttUploadRateValues":[

{
"valuelist":[
"1,10,no,17,398",
"1,10,no,25,620",
"2,12,yes,79,37242",
"2,12,yes,999,42306"
],
"fromDate":"2021-05-19T00:00:00",
"isBasic":true,
"tableId":4
}
]
}
}
 

Stefan

Well-Known Member
Yes, although with your structure you will need to disect the json yourself using JsonObject and JsonArray.
 

Latika6

New Member
Thank you Stefan! Is it possible by creating 2 separate temp-tables and a relation between them-

define dataset dsPrRateValueUpload for ttUploadRateValues, ttUploadRateValues1
data-relation valuedata for ttUploadRateValues, ttUploadRateValues1
relation-fields(tableId, tableId) nested foreign-key-hidden.

I tried read-json on the dataset mentioned but that fails
 

Stefan

Well-Known Member
Of course it fails, that's why you need to disect the json yourself using JsonObject and JsonArray - look these classes up on docs.progress.com

google site:docs.progress.com JsonObject
google site:docs.progress.com JsonArray
 

TomBascom

Curmudgeon
If you want an easy way to understand why READ-JSON fails, create and populate some examples of the temp-tables that you would like to read in to and, instead of reading in to them, write their contents out with WRITE-JSON. You will see that the WRITE-JSON structure is quite different from what you have posted. Which means that you have two choices - get the party creating the JSON to create it the way that you want to read it, or use a tool that can read it and process it as it actually is. JsonObject and JsonArray will process what you have shown. READ-JSON will not.
 

Bounty

New Member
I am not quite sure what you try to accomplish, but the following will read the json example you have posted.

Code:
DEFINE TEMP-TABLE ttUploadRateValues NO-UNDO
  FIELD valuelist AS CHARACTER EXTENT 4
  FIELD fromDate  AS DATETIME
  FIELD isBasic   AS LOGICAL
  FIELD tableId   AS INTEGER.
 
DEFINE DATASET dsPrRateValueUpload
  FOR ttUploadRateValues.

DATASET dsPrRateValueUpload:READ-JSON("file", "test.json", "empty").
 

Stefan

Well-Known Member
I am not quite sure what you try to accomplish, but the following will read the json example you have posted.
The question title:

Is it possible to store a json file into master-child temp-tables. I wish to store the comma separated list in the child table as separate records
But, using your example and manually creating the child records based on the extent, may be easier but also more error prone, than digging into how JsonObject and JsonArray work.
 
Last edited:

Latika6

New Member
I am not quite sure what you try to accomplish, but the following will read the json example you have posted.

Code:
DEFINE TEMP-TABLE ttUploadRateValues NO-UNDO
  FIELD valuelist AS CHARACTER EXTENT 4
  FIELD fromDate  AS DATETIME
  FIELD isBasic   AS LOGICAL
  FIELD tableId   AS INTEGER.
 
DEFINE DATASET dsPrRateValueUpload
  FOR ttUploadRateValues.

DATASET dsPrRateValueUpload:READ-JSON("file", "test.json", "empty").
I wanted to store valuelist in a separate table, so that the valuelist given as an array is stored as separate records and not hardcode the extent as 4. The above code helps me in reading the json in a single temp-table (I want to store the valuelist in a separate table and then link both tables on tableId)
 

Latika6

New Member
The question title:


But, using your example and manually creating the child records based on the extent, may be easier but also more error prone, than digging into how JsonObject and JsonArray work.
Yes, I am trying to dig out out on how Json Object and Array works. If I simply use read-Json, the store the extent as an array and moreover I do not want to hard code the array as 4
 

Latika6

New Member
If you want an easy way to understand why READ-JSON fails, create and populate some examples of the temp-tables that you would like to read in to and, instead of reading in to them, write their contents out with WRITE-JSON. You will see that the WRITE-JSON structure is quite different from what you have posted. Which means that you have two choices - get the party creating the JSON to create it the way that you want to read it, or use a tool that can read it and process it as it actually is. JsonObject and JsonArray will process what you have shown. READ-JSON will not.
Hi Tom, read-json and write-json was pretty simple to use..created a json format from write-json, with the help of data in the temp-tables. This format was different from the one I originally had. and this could read the data in different tables.
 
Top