extract data from redhsift

Question

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 ...

Why are you trying to extract JSON from a text field in a database? Wouldn't it be more sensible to process the JSON before you stored it in the database, so that you can use, I dunno, a structured query language, to pick the elements you want? That said, have you looked at the native JSON parsing functions in Redshift? docs.aws.amazon.com/redshift/latest/dg/… — MatBailie
this data is loaded by another vendor in our database directly. but now, i would need to extract from this. I tried json parsing and its failing with json parsing error. — George
Then show us the query you tried and the error you received! — MatBailie
select id, json_extract_path_text(ELEMENTS, 'name') as name1 from table . error:[XX000][500310] [Amazon](500310) Invalid operation: JSON parsing error Details: ----------------------------------------------- error: JSON parsing error code: 8 ... — George
Please edit your question, don't add code as a comment. Please include the error message in the question. Please confirm whether the text column contains strings starting with elements [ or if it just starts with [ and elements is just the column name. Ideally, you should give enough information that someone could exactly reproduce your error; stackoverflow.com/help/mcve — MatBailie

Nav Nav · Accepted Answer · 2018-08-28T13:57:15

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":"*****"}]}

extract data from redhsift

1 Answers