How to create a JSON from table data

[Flo]

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

[exa-Lennart]

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

[steffen_check24]

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"}

[exa-Lennart]

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 🙂