extract values from varchar in redshift sql

问题: SQL to fetch a value from varchar.I have a column elements as varchar in redshift as below Id elements 1 [{"name":"email","value":"abc@gmail.com","nodeName":"INPUT","ty...

问题:

SQL to fetch a value from varchar.I have a column elements as varchar in redshift as below

Id   elements
1 [{"name":"email","value":"abc@gmail.com","nodeName":"INPUT","type":"text"},{"name":"password","value":"*****","nodeName":"INPUT","type":"password"},{"name":"checkbox","value":null,"nodeName":"INPUT","type":"checkbox"}]

How do I select the value in this array above ie. abc@123


回答1:

If database of MYSQL, you should use

json_extract

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

SELECT 
    ID,
    json_extract(JSON_DATA, '$[0].value') AS MAIL
FROM JSON_TABLE;

Result:

ID  MAIL 
1   "abc@gmail.com"

Demo: http://rextester.com/live/SQG30833

Redshift should probably use json_extract_path_text Function https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html

  • 发表于 2018-08-31 19:54
  • 阅读 ( 226 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除