How to query dictionary type column values in SQL?

Question

I have a table looking like:

uuid  investors
1     [{"type": "Organization", "uuid": "435847ec34f541c3b66615de1f346534"}, {"type": "Organization", "uuid": "8bad8a2d97ba4d3c716686244058858e"}]
2     [{"type": "Person", "uuid": "9603633439264ebfb3e46e6052055893"}, {"type": "Organization", "uuid": "a3734663f2d34893afeeda74da26683a"}]

I want to query the uuid inside the list of column investors based on if it is Organization and Person as I have to later look for those uuid in these tables.

EDIT

thanks to @tcadidot0's answer, I now can get two columns containing type value and uuid value. Here is a screenshot of the results. Now, based on type, I want to access the corresponding tables and get the whole row having this particular uuid. Following is how the tables, Organization and Person looks like:

ORGANIZATION TABLE

uuid                              name
435847ec34f541c3b66615de1f346534  XYZ

PERSON TABLE

uuid                              name
9603633439264ebfb3e46e6052055893  Bob

I have no idea actually, I tried subquerying but I think it is not for my case. — Rishabh Sahrawat
– Rishabh Sahrawat, Commented Mar 6, 2020 at 8:37
@Cid I want to get uuid from the column investors and must know if it has type: Organization or Person — Rishabh Sahrawat
– Rishabh Sahrawat, Commented Mar 6, 2020 at 8:45
Can you edit your question and show us what should be the result of that query, given the sample input — Cid
– Cid, Commented Mar 6, 2020 at 8:53

FanoFN · Accepted Answer · 2020-03-06 08:51:25Z

2

I see your data is JSON. Try this query :

SELECT JSON_EXTRACT(investors, '$**.uuid') AS `uuid`,
       JSON_EXTRACT(investors, '$**.type') AS `Type`
FROM   mytable;

Like in this fiddle : https://www.db-fiddle.com/f/8roowVBt5VY6AebWHav9hb/1

I got this answer partially from here : https://stackoverflow.com/a/45671728/10910692

answered Mar 6, 2020 at 8:51

FanoFN

7,1442 gold badges16 silver badges35 bronze badges

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

4 Comments

Rishabh Sahrawat Over a year ago

Thanks for your answer, let me give it a try.

Rishabh Sahrawat Over a year ago

This works and returns two columns, uuid&Type, now I want to look for this uuid in other table named Organization or Person. Can I do that using subquerying? or is there a better way? Thank you!

FanoFN Over a year ago

I'm not sure how exactly but you can add WHERE JSON_EXTRACT(investors, '$**.type') LIKE '%Person%' or something like that. I think with this example you can edit your question with your expected output to make us better understand. Just make a screenshot on spreadsheet etc. to illustrate how do you want the result returned.

Rishabh Sahrawat Over a year ago

Thank you again. I edited my question, please have a look.

Collectives™ on Stack Overflow

How to query dictionary type column values in SQL?

ORGANIZATION TABLE

PERSON TABLE

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

ORGANIZATION TABLE

PERSON TABLE

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related