If you can insure the JSON will always be less than 4000 characters you can use a single VARCHAR2 field to store the whole thing.

If the file can be larger, then you are out of luck, as custom fields cannot be CLOBs as far as I know, and I think the size limit is 4000. In that case you would need to model the JSON structure into one or more CLUs and save the data related by how the hierarchy flows. It’s not particularly straightforward since JSON can have complex objects inside other complex objects and supports arrays of data with variable lengths to the collections. That’s why JSON (and XML) content is often stored as one big LOB of text, then it is deserialized as needed by advanced parsers that can handle the structural complexity of non-tabular data.

What is the structure of the JSON? Is it simple, or complicated?

Thanks,

Joe Kaufman

Thanks for replay Joe!

Structure of json is like that:

{"Results": 
    {"WebServiceOutput0": 
        �
            {
                "feature1": 0.7403482493914997, 
                "feature2": 1.015384615384623, 
                ... #another 400 other float type features on the same level(row)
                "Scored ": 39.0
            },
            {
                "feature1": 0.3403482493914997, 
                "feature2": 1.547894615384623, 
                ... #another 400 other float type features on the same level(row)
                "Scored": 128.0
            },
            ... #another rows. There could be up to 500 rows in single file. Each row could have aprox 26 500 characters
            {
                "feature1": 0.5503482493914997, 
                "feature2": 1.235444615384623, 
                ... #another 400 other float type features on the same level(row)
                "Scored": 22.0
            }
        ]
    }
}

I want to save that many rows in custom table that I have in json file. For each row I want to save each feature in a separate column of custom table(*let's suppose I have the table with the appropriate structure - the same structure as rows in json file)

In that case you would need to model the JSON structure into one or more CLUs and save the data related by how the hierarchy flows.

how to save the data?

best regards

Bart

The data looks fairly simple to model, columns for all the “feature” fields and the “Scored” value.

I do not know how to transform JSON returned by IFS Connect into rows in a CLU. When it comes to that, I would use our custom application (written in C#) to get the JSON (via the API web call), deserialize the JSON, and add rows to the CLU -- one row per deserialized JSON node.

An IFS Connect expert would need to weigh in on how to perform such a translation in IFS itself…

Thanks,

Joe Kaufman

Hello Joe,

Thanks for the responds. I undestod that this case cann’t be handle without modification. The correct way to handle this requirement is transform json to XML and then save the XML in database using BizAPI provided by modification. I hope in the future IFS will provide some configurable custom solution for problems like this, because they will be very common. 

Best regards

Bart