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}
-
1Just got a pure SQL version working.David East– David East2012-06-13 19:26:58 +00:00Commented Jun 13, 2012 at 19:26
Add a comment
|
2 Answers
Hive actually has a command just for that get_json_object
Comments
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.
