CODEWITHSUNDEEP
sqljsonparsinghive
Don P
Don P

Reputation: 63637

Parse JSON string in SQL

I have a column of JSON strings in my SQL table. I want to extract the 'page' value, any idea how? {"action.type":"click","page":1424}

Upvotes: 1

Views: 4380

Answers (2)

David East
David East

Reputation: 32604

Here is a pure SQL version.

DECLARE @json varchar(64);
DECLARE @index int;
SELECT @json = '{"action.type":"click","page":1424}';
DECLARE @length int = LEN(@json);
DECLARE @pageIndex int = CHARINDEX('page":', @json);
DECLARE @difference int = @length - (@pageIndex + 6); -- 6 is for page":
SELECT @index =  CHARINDEX('page', @json);
SELECT SUBSTRING(@json, @index + 6, @difference);

This will give you a result of 1424

It is really long-winded, but it shows step-by-step how it get's that value. You can easily refactor that into a stored procedure.

Upvotes: 2

Nik
Nik

Reputation: 472

Hive actually has a command just for that get_json_object

Upvotes: 2

Related Questions