sql - What to get the data From a column based on the JSON Tag Stored in the Column


Keywords:sql 


Question: 

I have a Hive table which stores the Data as JSON file name in one column and Full JSON on the Other Column.

lets say col1 has data.json and col 2 has JSON in it { "ID": "1", "Name": "ABC", "Dept":"market" }

I want to Build a Query where i can get the name from JSON on the basis of ID, Consider below as an example for the query which I want

select (SHOULD GIVE JSON TAG **NAME**) from temp where col1=data.json and col2 (JSON ID is 1)

It should Return ABC for the Above JSON Stored in Col2.


1 Answer: 

In Hive, you could use the get_json_object function.

SELECT get_json_object(col2, '$.Name') AS Name
FROM TEMP
WHERE col1 = 'data.json'
    AND get_json_object(col2, '$.ID') = 1;