英文:
In AWS redshift, the json_extract_path_text function return empty string instead NU
问题
In AWS Redshift, when using the json_extract_path_text function in a standalone select, it returns NULL as expected. However, when used in a select from a table, it returns an empty string (""). To make the function return NULL from a select table query, you can modify your SQL query like this:
select CASE WHEN json_extract_path_text(payload, 'AA') = '' THEN NULL ELSE json_extract_path_text(payload, 'AA') END from #test;
This query will check if the result is an empty string and return NULL if it is.
英文:
In AWS Redshift, I am using json_extract_path_text function to parse simple Json.
I deliberately try to find non exists element in the Json so the function should return NULL.
If I use the function in 'standalone' select, the function return NULL as expected but if the function is used in select from table then a string empty ("") returned.
Here a simple example:
select json_extract_path_text('{"A":"B"}', 'AA'); --> Return NULL as expected.
create table #test (
payload varchar(max)
);
insert into #test (payload) values ('{"A":"B"}');
select json_extract_path_text(payload,'AA') from #test; --> Return empty string
How can I make the function return null from select table query?
Thanks.
I try everything...
答案1
得分: 1
我向AWS发布了这个问题，他们批准了这是Redshift中的一个错误：
AWS支持：
"我已经与我们的内部团队联系过，他们提到不一致实际上是Redshift一侧的一个错误，仍在由服务团队处理，并将在即将发布的补丁中解决"。
英文:
I posted to AWS the issue and they approved that this is a bug in Redshift:
AWS support:
"I have been in contact with our internal team and they mentioned that the inconsistency is actually a bug from Redshift side that is still being worked on by the Service team and will be addressed in the upcoming patches"
答案2
得分: 0
你需要使用 NULLIF 函数
select nullif(json_extract_path_text(payload,'AA'),'') from #test
英文:
You need to use the NULLIF function
select nullif(json_extract_path_text(payload,'AA'),'') from #test
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论