CODEWITHSUNDEEP
sqlhivehiveql
Anupam Alok
Anupam Alok

Reputation: 433

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

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.

Upvotes: 1

Views: 141

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

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;

Upvotes: 2

Related Questions