How to skip errors when using json_extract_path_text in Redshift?

Question

I have the following query:

SELECT 'curl -s http://www.mde.operator.com/MRE/api?profile=CANCEL_AUTH&mode=assync-oneway&Auth='||json_extract_path_text(external_reference_id,'transactionIdAuth') + '&NUM=' + phone  FROM dbo.cancelled WHERE id like '%Auth%';

It will bring more than 60 thousands results, but the json is broken and I cannot manage to delete the broken lines.

Is there any way to skip the rows which shows any kind of errors?

Note: It isn't null rows.

I've already try:

json_extract_path_text(regexp_replace(event_properties,'\\\\.',''),'someValue')

Hi, anyone can give me a clue about this problem?

Daniel
– Daniel

2018-02-04 13:06:56 +00:00
Commented Feb 4, 2018 at 13:06 — Daniel
– Daniel, Commented Feb 4, 2018 at 13:06

Alessandro Cosentino · Accepted Answer · 2018-02-04 16:24:31Z

3

You can make them null rows though, by setting the null_if_invalid argument of the json_extract_path_text function to true.

Source: https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html

answered Feb 4, 2018 at 16:24

Alessandro Cosentino

2,3561 gold badge22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to skip errors when using json_extract_path_text in Redshift?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related