Extract specific data from JSON returned in query - return that data in their own columns

Support Usage Support
,
1

Hello,
One of the columns returned from my query is full of JSON data (contacts_list)
Is there anything I can write into the initial query that will return these 3 columns instead?:
cust_id, name, email

Essentially I need to extract the name and email from contacts_list and display it in the results with columns for name and email so I can export that info.

thanks!!

Screen Shot 2022-08-16 at 1.35.40 PM
Screen Shot 2022-08-16 at 1.35.40 PM1033×577 30.8 KB

2

Thanks for your question and welcome to the forum! What kind of database are you querying? Your query is being passed directly to that database. So you can use any of the JSON handling functions of that DB.

1 Like
3

I’ve been searching how to parse json data from a SQL query but I’m not getting back anything that makes sense to use with redash. I’m trying to do the following:

SELECT cust_id, contacts_list
FROM some_database
WHERE cust_id = 1234567
then get name and email from contacts_list
return a table with columns for cust_id, name, email INSTEAD of what's in the attached picture

My SQL knowledge is as basic as it gets so maybe I’m just misunderstanding the information I’m googling, but is there anyone who can show me what the rest of that query needs to be in order to achieve my goal? When you ask what type of DB, can you give me a few examples so I can find out?

4

My SQL knowledge is as basic as it gets so maybe I’m just misunderstanding the information I’m googling

I agree. This is a simple question you’re asking. Once we know the kind of database you use it will be a lot easier to point you to a solution :ok_hand:

but is there anyone who can show me what the rest of that query needs to be in order to achieve my goal?

That will depend on what kind of database you use. The syntax for JSON operations is different from one database to another.

When you ask what type of DB, can you give me a few examples so I can find out?

Examples would be Postgres, Mysql, Oracle etc…

5

I was told Presto DB.
Thanks so much for your patience with me

6

Awesome! Presto has a robust collection of JSON functions, documented here: JSON Functions and Operators — Presto 0.275 Documentation

For your use case you’ll want the json_extract or json_extract_scalar functions.

7

Just re-reading back on this to get that link again and realizing you asked me about the database about 4 times before I gave an answer … many apologies and THANK YOU for your patience on that one :slight_smile: :-).