Reputation: 63788
Using SQL, how can I select the value for "conversion_event" from the JSON below?
{"str":[1,1342886173,100000627571405,"offsite_conversion.lead",{"action.type":"offsite_conversion","conversion_event":387756207950188,"tag":"lead"},["conversion_event"],[],{"amount":12623486},"1:11:1:0:0:1:0"]}
There are some unusual things in here, for example JSON within JSON, and the square brackets. Assume that lengths of all values vary by row, so you cannot slice by a set number of character positions.
Upvotes: 1
Views: 371
Reputation: 70528
Based on comments:
declare @astr varchar(max);
declare @start int;
declare @end int;
set @astr = '{"str":[1,1342886173,100000627571405,"offsite_conversion.lead",{"action.type":"offsite_conversion","conversion_event":387756207950188,"tag":"lead"},["conversion_event"],[],{"amount":12623486},"1:11:1:0:0:1:0"]}';
select @start = charindex('"conversion_event":',@astr)
select @end = charindex(',"tag":',@astr)
select substring(@astr,@start,@end-@start);
returns
"conversion_event":387756207950188
add
set @start = @start + 19;
to get just the number.
SELECT substring('{"str":[1,1342886173,100000627571405,"offsite_conversion.lead",{"action.type":"offsite_conversion","conversion_event":387756207950188,"tag":"lead"},["conversion_event"],[],{"amount":12623486},"1:11:1:0:0:1:0"]}',
101,16);
or
select substring('{"str":[1,1342886173,100000627571405,"offsite_conversion.lead",{"action.type":"offsite_conversion","conversion_event":387756207950188,"tag":"lead"},["conversion_event"],[],{"amount":12623486},"1:11:1:0:0:1:0"]}',
151,16)
Ok this is the structure of the object:
{
"str":[1,
1173,
10005,
"offsite_conversion.lead",
{"action.type":"offsite_conversion",
"conversion_event":387756207950188,
"tag":"lead"},
["conversion_event"],
[],
{"amount":14486},
"1:11:1:0:0:1:0"
]}
An object with an atribute str
which is an array.
The 5th element has a 2nd attribute conversion event
The 6th element is an array of one element which is conversion event.
So the question is... is this structure the same and which of these do you want?
Upvotes: 1