06-10-2020 10:41 AM
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
06-10-2020 04:21 PM
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
07-10-2020 09:09 AM
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:
SSO | TO_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"} |
08-10-2020 12:56 PM
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 🙂
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In