Redshift - json functions don't exist

Question

Starting sometime last night, all of my queries that use json_extract_path_text stopped working. They had been working fine for months. Now when I try a query like this:

select json_extract_path_text(json_data, 'Legal Last Name:') legal_last_name from psd_warehouse.raw_data.raw_sheets_comp_staff_dir_aes

I get the following error:

ERROR: function json_extract_path_text(super, "unknown") does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

Anyone have any idea why the functionality would have changed?

Thanks!

Answer

Hello,

Thank You for your question.

I have reached out to the Redshift service team and they have not indicated any recent change to this functions; parameters or functionality.

The function is expecting a 'json_string' and a super column is provided, you may use the json_serialize function to prepare your super column data for use with any of the json function expecting a 'json_string' as an input parameter.
https://docs.aws.amazon.com/redshift/latest/dg/JSON_SERIALIZE.html

for example:
json_extract_path_text(json_serialize(super_column), 'path_elem' )

If you have any further questions I would invite you to open a support case to further investigate and escalate if required.

Thank You and have a great day.

Redshift - json functions don't exist

Relevant questions