3

Wondering if anyone working in SFMC/Marketing Cloud Engagement has a method for extract a variable number of array property values from a JSON string in a Data Extension row.

I'm aware that the ideal solution leverages the OPENJSON library/function, but it isn't supported in the platform.

Here's my brute-force method that works. I would love to not hard-code a fixed number of array elements in JSON string (the # of union-ed sub-queries below).

Is there a shorter, scalable method that I can leverage?

CREATE TABLE SubscriberRoles
    ( subscriberKey varchar(255), roles varchar(max))
;
INSERT INTO SubscriberRoles
     (subscriberKey,roles)
VALUES
     ('12345','[{"Category":"Accounting","City":"Denver","State":"CO","Zip":"12345"},{"Category":"Management","City":"Denver","State":"CO","Zip":"12345"}]')
   , ('24680','[{"Category":"Accounting","City":"Kansas City","State":"MO","Zip":"66555"},{"Category":"Sanitation","City":"Kansas City","State":"KS","Zip":"66866"},{"Category":"IT","City":"Springfield","State":"MO","Zip":"71234"}]')     
   , ('35790','[{"Category":"IT","City":"Springfield","State":"MO","Zip":"71234"}]')     

select
  si.subscriberkey
, i.Category
, i.City
, i.State
, i.Zip
from SubscriberRoles as si
left join (

        select subscriberkey, json_value(roles, '$[0].Category') as Category, json_value(roles, '$[0].City') as City, json_value(roles, '$[0].State') as State, json_value(roles, '$[0].State') as Zip from SubscriberRoles
  union select subscriberkey, json_value(roles, '$[1].Category') as Category, json_value(roles, '$[1].City') as City, json_value(roles, '$[1].State') as State, json_value(roles, '$[1].State') as Zip from SubscriberRoles
  union select subscriberkey, json_value(roles, '$[2].Category') as Category, json_value(roles, '$[2].City') as City, json_value(roles, '$[2].State') as State, json_value(roles, '$[2].State') as Zip from SubscriberRoles
  union select subscriberkey, json_value(roles, '$[3].Category') as Category, json_value(roles, '$[3].City') as City, json_value(roles, '$[3].State') as State, json_value(roles, '$[3].State') as Zip from SubscriberRoles
  union select subscriberkey, json_value(roles, '$[4].Category') as Category, json_value(roles, '$[4].City') as City, json_value(roles, '$[4].State') as State, json_value(roles, '$[4].State') as Zip from SubscriberRoles

) i on i.subscriberkey = si.subscriberkey

where 1=1
and i.Category is not null

Output

subscriberkey Category City State Zip
12345 Accounting Denver CO CO
12345 Management Denver CO CO
24680 Accounting Kansas City MO MO
24680 IT Springfield MO MO
24680 Sanitation Kansas City KS KS
35790 IT Springfield MO MO

Try it yourself: dbfiddle

0

You must log in to answer this question.

Browse other questions tagged .