How to get value from json nested data?

Continuing the discussion from How to query json data?:

Hi Everyone,

i would like to ask more question.

  1. how to query from nested data i want page_location data?
  2. how to split text and get value from sc_camp ?

this is my query

SELECT eventparams.key,
eventparams.value.string_value, * FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) limit 1000

please see detail in photo below.

Many thanks,
Siriphon.

Hi @Siriphon - There are 2 steps involved here.

As a first step, can you please provide the result of the below query.

SELECT eventparams.key,
eventparams.value.string_value, * FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) 
where key='page_location' and string_value like '%sc_camp%'
limit 1000
1 Like

This is a result , i’ve adjust qry a bit

SELECT eventparams.key,

eventparams.value.string_value,

  • FROM “scbpt_dl_persistent”.“ga_dib_history”

CROSS JOIN UNNEST(event_params) as t(eventparams)

where eventparams.key = ‘page_location’ and eventparams.value.string_value like ‘%sc_camp%’

limit 1000

Could you please suggest extract value from sc_camp ?

Hi @Siriphon - Thanks. Can you try the below sql.

SELECT eventparams.key,
eventparams.value.string_value, 
split(eventparams.value.string_value,'sc_camp=',1) as sc_camp_value,
* FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) 
where key='page_location' and string_value like '%sc_camp%'
limit 1000

Regards - Sanjeeb

1 Like

i can qry but sc_camp_value doesn’t display on data set

Hi @Siriphon - You have a * in the sql. Can you run the below query.

SELECT eventparams.key,
eventparams.value.string_value, 
split(eventparams.value.string_value,'sc_camp=',1) as sc_camp_value
 FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) 
where key='page_location' and string_value like '%sc_camp%'
limit 1000

Regards - Sanjeeb

1 Like


it show only 2 filed key and string_value

Hi @Siriphon - Thanks. Can you please run the below query. Since string_value is a big one, it is not showing , Let’s see run the below query.

SELECT eventparams.key,
split(eventparams.value.string_value,'sc_camp=',1) as sc_camp_value
 FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) 
where key='page_location' and string_value like '%sc_camp%'
limit 1000

Regards - Sanjeeb

2 Likes

it show only key.

Hi @Siriphon - Can you please check the Dataset tab as well.

Regards - Sanjeeb

1 Like

Hi @Siriphon - In case of it is not working, is it possible to create a new data set with the custom sql and click apply and see whether the result is coming or not.

Regards - Sanjeeb

1 Like


This is a result.

Hi @Siriphon - Thanks. Can you do the below query.

SELECT eventparams.key,
split(eventparams.value.string_value,'sc_camp=',2) as sc_camp_value
 FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) 
where key='page_location' and string_value like '%sc_camp%'
limit 1000

If it is not working, there is a second option, can we create a calculated field. to do the same.

Step -1: In the data set page, click Add, Add a calculated field.
Step -2: Paste the below formula.

split({string_value},'sc_camp=',2)

and click Save. See the below screenshot for the same.

I tested from my end and it is giving me right info.

Regards - Sanjeeb

1 Like

it working but i need only value behind sc_camp , like a row number 3 .

Hi @Siriphon - Can you please update the split logic by the below one

split(split({string_value},'sc_camp=',2),'&',1)

See the screenshot from my end.

Regards - Sanjeeb

1 Like

it working but some value have % in row number 3 .

Hi @Siriphon - Ok, it is a very interesting problem. Couple of questions.

  1. Are you expecting only & and % after the sc_camp_value or it may be any character?
  2. Do we have a fixed length for the sc_camp_value, if yes, what is the length.

Please give the above details so that we can see which option will be the best suite in your case.

Regards - Sanjeeb

1 Like

Hi @Siriphon - Can you try the below logic for the calculated field.

ifelse(contains(split(split({string_value},'sc_camp=',2),'&',1),'%'),
split(split({string_value},'sc_camp=',2),'%',1),split(split({string_value},'sc_camp=',2),'&',1))

Regards - Sanjeeb

1 Like
  1. Are you expecting only & and % after the sc_camp_value or it may be any character?
    yes only & and %

2 Probably 30-32 length.

Many thanks

1 Like

Thanks @Siriphon - Please put the logic which is provided in my previous post. Hope this will solve the puzzle.

ifelse(contains(split(split({string_value},'sc_camp=',2),'&',1),'%'),
split(split({string_value},'sc_camp=',2),'%',1),split(split({string_value},'sc_camp=',2),'&',1))

Regards - Sanjeeb

2 Likes