1

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.

Improve this question
5
  • Do you really have to do this in SQL? Like trying to hammer a nail with a car engine
    – Bort
    Commented Jul 23, 2012 at 23:28
  • There are two which one did you want?
    – Hogan
    Commented Jul 23, 2012 at 23:30
  • I've tried to manipulate it using 'LATERAL VIEW JSON_TUPLE' but this sounds like a solution specific to Hive, and not the best way in SQL.
    – Don P
    Commented Jul 23, 2012 at 23:32
  • @Bort unfortunately, I do need to do it using SQL.
    – Don P
    Commented Jul 23, 2012 at 23:32
  • What database are you using? If this is TSQL you could write a CLR function to do this no problem, I don't know about others
    – Bort
    Commented Jul 23, 2012 at 23:40

1 Answer 1

Reset to default
1

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?

Improve this answer
5
  • Thanks Hogan - but unfortunately the length of many of the values will vary by row, so I cannot use set positions for slicing.
    – Don P
    Commented Jul 23, 2012 at 23:31
  • :P I want the value for the first conversion_event, in this case "387950188".
    – Don P
    Commented Jul 23, 2012 at 23:37
  • So you want 387756207950188? And the structure does not change?
    – Hogan
    Commented Jul 23, 2012 at 23:40
  • correct. I always want the value that is keyed by the first "conversion_event"
    – Don P
    Commented Jul 23, 2012 at 23:42
  • Good call to just match the strings.
    – Don P
    Commented Jul 24, 2012 at 0:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.