Clement P
asked on
JSON_EXTRACT_PATH_TEXT Function
Hi,
I've a table on AWS Redshift with couple of columns in JSON format.
So i'm using JSON_EXTRACT_PATH_TEXT Function to query the data in the JSON format cloumn.
For example ,
SELECT user,json_extract_path_tex t(payload, 'code') as payload_code FROM activity_log.lx4_logs
where event_reference LIKE 'My Dashboard%'; .
So the output look like following with two coloumns
User | Code
user1 | ["13877512003","1387764200 3","100784 02003","13 901222003" ,"10118722 003"]
user2 |["105700520150300750","10 6468920140 600750","1 1227922015 0300750"," 1046931200 60101500", "122805820 150600750" ]
user3 |NULL
user4 |["10112472015"]
Is there a possibility to get the output, into rows like below without any quotation, square brackets etc please ?
User | Code
user1 |13877512003
user1 |13877642003
user1 |10078402003
user1 |13901222003
user1 |10118722003
user2 |105700520150300750
user2 |106468920140600750
user2 |112279220150300750
user2 |104693120060101500
user2 |122805820150600750
user3 |NULL
user4 |10112472015
Thanks
I've a table on AWS Redshift with couple of columns in JSON format.
So i'm using JSON_EXTRACT_PATH_TEXT Function to query the data in the JSON format cloumn.
For example ,
SELECT user,json_extract_path_tex
where event_reference LIKE 'My Dashboard%'; .
So the output look like following with two coloumns
User | Code
user1 | ["13877512003","1387764200
user2 |["105700520150300750","10
user3 |NULL
user4 |["10112472015"]
Is there a possibility to get the output, into rows like below without any quotation, square brackets etc please ?
User | Code
user1 |13877512003
user1 |13877642003
user1 |10078402003
user1 |13901222003
user1 |10118722003
user2 |105700520150300750
user2 |106468920140600750
user2 |112279220150300750
user2 |104693120060101500
user2 |122805820150600750
user3 |NULL
user4 |10112472015
Thanks
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
I am here to help you with your open question. Do you still need help? I have the ability to alert more experts if you still need help.
If you solved the problem on your own, would you please post the solution here in case others have the same problem?
If you need me to delete this question just say "Delete."
Thank you for using Experts Exchange.
Regards,
Kyle Santos
Customer Relations