How to create a JSON from table data

Padawan

We would like to create a JSON from a table (or a SELECT statement) with a function like “to_json”. There won't be any level structures / hierarchies inside the JSON.

 

Given table:  a.table

ID

Name

Age

Is_test

100

Müller

38

true

200

Maier

Null

false

 

IDEA:


SELECT ID, to_json( Name, Age, Is_test)
FROM a.table

 

Output:

ID

JSON

100

{ “Name”: “Müller”, “Age”: 38, “Is_test”: true}

200

{“Name”: “Maier”, “Age”: null, “Is_test”: false}

 

JSON structure:

“id”: 100
“Name”: “Müller”
“Age”: 38
“Is_test”: true


“id”: 200
“Name”: “Maier”
“Age”: null
“Is_test”: false

 

 

Anyone ever done anything simliar?

Thanks in advance,

 

Flo

3 REPLIES 3

Team Exasol
Team Exasol

Hi Flo,

welcome to the Exasol community.

What you are asking for is basically the reverse of our recently implemented JSON functions. Because those functions only feature JSON -> relational structure operations and not the reverse operation those functions won't help you here. Instead you can use the limitless possibilities of our UDF framework. In order for your idea to work to_json needs to be a SCALAR RETURNS VARCHAR(2000000) function. This function then reads data from a.table in row by row and returns one JSON string.

You can even write that to_json function so generic that it can handle an arbitrary number of columns when using a SELECT to_json(*) FROM a.table.

If you want to share a prototype with the community that would be greatly appreciated by other users 🙂

Have a good one!
Lennart

Contributor

Good morning Lennart,

thanks for your response, we are allready using the new json functions heavily, great stuff 😀 !

We built a first prototype for creating a flat key/value pair json, please see the code below. We did not manage so far, to pass the Exasol column name to the function, so we used a workaround, to pass the values for the json keys as hardcoded input parameters. With our version, you need to pass the column , followed by a string to use as a key. 

Does the job for the moment, but we are still looking for a way, to create the keys from Exasol column name dynamically. The exa.meta.input_columns[x].name function gives just a numbering of columns as a result, not the names of the Exasol table. Any ideas, how to solve that?

 

Cheers,

Steffen

 

Create or replace python3 scalar script EXA_TOOLBOX.TO_JSON(...) RETURNS VARCHAR(2000000) AS
# Function to convert table coloumns into a 1 level json structure with a flat list of key value pairs
# Parameters: <coloumn a>, <key for coloumn a as string>, <coloumn b>, <key for coloumn b as string>
import sys
def run(ctx):
# check that there is a even number of parameters
if exa.meta.input_column_count % 2 !=0:
sys.exit('ERROR: Funktion To_json requires even number of parameters')
for i in range(1,exa.meta.input_column_count,2):
if str(exa.meta.input_columns[i].type).find('str') < 0:
sys.exit('ERROR: Every second parameter defines the json key and needs to be of type VARCHAR')
json_string = '{'
for i in range(0,exa.meta.input_column_count-1,2):
if ctx[i] is None:
value = 'null'
elif str(exa.meta.input_columns[i].type).find('str') >=0:
value = '"' + ctx[i] + '"'
elif str(exa.meta.input_columns[i].type).find('bool') >=0:
value = str(ctx[i]).lower()
elif str(exa.meta.input_columns[i].type).find('datetime.date') >=0:
value = '"' + str(ctx[i]) + '"'
elif str(exa.meta.input_columns[i].type).find('int') >=0:
value = str(ctx[i])
elif str(exa.meta.input_columns[i].type).find('datetime.datetime') >=0:
value = '"' + str(ctx[i]) + '"'
else:
sys.exit('ERROR: Data type not defined')
if i == exa.meta.input_column_count-2:
json_string = json_string + '"' + ctx[i+1] + '":' + value
else:
json_string = json_string + '"' + ctx[i+1] + '":' + value + ','
json_string = json_string + '}'
return json_string
/

Sample: 

SELECT ID, EXA_TOOLBOX.TO_JSON(F.DATE_OF_BIRTH, 'DATE_OF_BIRTH' , F.POSTAL_CODE, 'postal_code')
FROM MY_TABLE F LIMIT 2; 

Result:

SSOTO_JSON(F.DATE_OF_BIRTH,'date_of_birth',F.POSTAL_CODE,'postal_code')
123456{"date_of_birth":"1984-12-04","postal_code":null}
777777{"date_of_birth":"1990-09-15","postal_code":"99999"}

Team Exasol
Team Exasol

Hi Steffen,

I would strongly recommend you to use a python dictionary as buffer for your JSON string, as this data-structure is already JSON compliant. Then use json.dumps() to return an encoded JSON string.

 

exa.meta.input_columns[i].name

Contains the name of the input column i.

Hope this helps 🙂