CODEWITHSUNDEEP
sqljsonamazon-redshift
George
George

Reputation: 21

extract data from redhsift

elements
[{"name":"email",
  "value":"[email protected]",
   "nodeName":"INPUT",
   "type":"text"}, 

 {"name":"password",
  "value":"*****",
  "nodeName":"INPUT",
  "type":"password"}, 

  {"name":"checkbox",
   "value":null,
   "nodeName":"INPUT",
   "type":"checkbox"}]

I have data like this in redshift. How do I extract value [email protected] from this. This query is for redshift. Please help me with the SQL. Elements is a column name and the value starts with [].

Query I tried: select id, json_extract_path_text(ELEMENTS, 'name') as name1 from table

error:[XX000][500310] Amazon Invalid operation: JSON parsing error Details: ----------------------------------------------- error: JSON parsing error code: 8 ...

Upvotes: 0

Views: 192

Answers (1)

Nav
Nav

Reputation: 1394

You can create UDF in python, for your case I've created one, please test and edit as suits:

create or replace function f_py_json (jsonVar varchar(512), 
jsonElemvarchar(10), occ integer)
  returns varchar(512)
stable
as $$
  import json
  asJson = json.loads(jsonVar)
  name_list = []
  ret=str(asJson['elements'][occ][jsonElem])
  return ret
$$ language plpythonu;

select f_py_json (id, 'value', 1) from test;

-- Input is {"elements":[{"name":"email","value":"[email protected]"},{"name":"password","value":"*****"}]}

Upvotes: 0

Related Questions