Export data to json

Help Needed
1

We are currently performing imports using data from https requests in json format, we do so, by using extensively json_extract, great feature.

Now we have the situation, that we should perform an export over soap submitting the data in json format.

Of course we can develop a UDF which generates a json structure from a given Query using python. However, I was wondering whether there would also be a built in solution like for parsing json, that would make it much simpler.

In case we have to build it from scratch we would need to do some thinking, because we would like to have a generic approach, which could be used for different use-cases without the need to develop each time everything.

I guess a simple json could use as input a table/view object and just transform it into json using the table and column names.

It seems a bit more complicated when you have to develop nested json structures. We would probably have all the information in de-normalized form and then would need to handle it, somehow we would probably need to specify the json-path for each element, using similar syntax as in json_extract. We will need to think a bit about this.

Has anybody ever had this situation/requirement and how have you solved it?

1 Like
2

@gmessmer Would be great if we could setup a call and talk about this topic.

3

Hello Thomas_bestfleisch

Thank you very much for your help. An exchange of ideas would be a great idea.

We would also be willing to try something out and then share our solution and experience with you and the community.

This week I would be available:

  • Mornings from 06:00 - 08:00
  • Tuesday/Wednesday/Thursday from 12:00 - 13:00
  • Thursday 14:30 - 16:00
  • Friday 08:30 - 10:30

Next week I will be on holiday, but could also make myself available for a phone call.

Kind Regards

Gallus

4

@gmessmer will send you an email with an invite

5

We developed a solution, which works for the 2 most urgent use cases, which we have. These are a bit special. One row leads to one json, we currently don’t have the case that multiple rows have to go into one json structure.

The main reason is that we have some dataprocessing in Exasol, the last step is then to update the core system with the findings, the SOAP Service which we call accepts only one Object (in our case Adresse) per call.

Here some details how it works

Usage of the solution

Prior to calling the webservice we have some complicated logic, which stores the result in a mart table (mart because we also need to use the information in a control-report).

The following statement goes against the mart and produces the json, which then gets submitted via soap-call/https-post (which we develop next)

SELECT export_schema.generate_json(

– Data-Elements

id, email, language, level_, CHANGE, change_Date, reason, email_uuid,

‘[{ “messageCategory”:“SPS”, “textModuleId”:“41fba3ad-e6bf-4f6e-9a46-a59d126aa0fa”}]’,

– JSon-Path expressions

‘$.bpasInformation.sourceSystemId’,

‘$.bpasInformation.channel’,

‘$.bpasInformation.businessEventId’,

‘$.bpasInformation.customerContactId’,

‘$.isFamilyHove’,

‘$.validFrom’,

‘$.address.changeReasonId’,

‘$.address.country’,

‘$.messageList’

)

FROM json_export_marts.akq_adv_level_hist;

Implementation details

The Method signature

CREATE OR REPLACE PYTHON3 SCALAR SCRIPT export_schema.generate_json(…) RETURNS varchar(2000000) AS

The function performs the following steps

  1. Tests whether the input parameters contain the values and the json-path-expressions
  2. Parses the parameters and constructs a composit object structures (see: Gang4 Composit-Pattern: Composite pattern - Wikipedia )
  3. Then all that needs to be done is call on the root-object the method to_json(), which recursively goes through the object-structure, and return the result

Further enhancements

One enhancement would be to extend this behavior to map multiple records to one json. For that we would require some grouping arguments to decide which records have to go into which json. We would also need to put some thoughts into the way arrays would work in such a n-to-1 scenario.

As we don’t have that scenario yet, we have not spent the time.

2 Likes