How to fetch string from a JSON array stored in Amazon Redshift column?

Question

I have a database stored in Amazon Redshift and an array is stored in table column in JSON format.

How to fetch a string from array?

Deeksha gupta · Accepted Answer · 2019-02-05 09:40:25Z

2

Using json_extract_path_text you can retrieve values from a column In Redshift database I have JSON in one column
This query performs join and to get seperate column results.

SELECT json_extract_path_text(O._doc,'domain') AS Domain, 
       json_extract_path_text(P._doc,'email') AS Email
FROM   intelligense_mongo.organisations AS O
INNER JOIN    intelligense_mongo.people AS P
ON  json_extract_path_text(O._doc,'_id') = 
    json_extract_path_text(P._doc,'organisation_id')
Where 
json_extract_path_text(O._doc,'tools_name') = '%"WordPress"%'

answered Feb 5, 2019 at 9:40

Deeksha gupta

6596 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Rotenstein · Accepted Answer · 2019-02-04 04:48:59Z

2

Use the JSON_EXTRACT_PATH_TEXT Function:

select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6');

json_extract_path_text
---------------------- 
star

answered Feb 4, 2019 at 4:48

John Rotenstein

273k28 gold badges455 silver badges540 bronze badges

Collectives™ on Stack Overflow

How to fetch string from a JSON array stored in Amazon Redshift column?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related