fez fez - 17 days ago 14
JSON Question

Redshift UDF - Returning JSON keys

I'm trying to write a Redshift UDF with Python to return the keys of an JSON object:

create or replace function json_keys (j varchar(max))
returns varchar(max)
stable as $$
import json
arr = json.loads(j)
for key in arr.keys():
return str(key)
$$ language plpythonu;


However when testing the function it only returns the last key in the object key i.e.

select json_keys('{"key1": "value1", "key2": "value2","key3": "value3"}') -- returns key3 only


What do I need to change to get it to return key1, key2 and key3?

Answer

Loop returns the last item. checkout this.

create or replace function json_keys (j varchar(max))
returns varchar(max)
stable as $$
    import json
    arr = json.loads(j)
    return '' if not arr else   ','.join(arr.keys())
$$ language plpythonu;

select json_keys('{"key1": "value1", "key2": "value2","key3": "value3"}')
Comments