问题:
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