How to retrieve JSON values from a Data Extension

Question

I have a basic javascript for retrieving values. Like this:

    <script runat="server">
    Platform.Load("core", "1.1");
    
    var list = ["value1","value2"];
    for (var i =0; i < list.length; i++) {
        Variable.SetValue("@element", list[i]);
    </script>
    
    %%[
        /* Put your AMPscript here */
        Output(Concat(@element, '<br>'));
    ]%%
    
    <script runat="server"> 
    }
    
    </script>

Now I have a data extension with a JSON in a attribute field (["value1","value2"]). How can I adjust my script so I got the JSON data from that data extention? And is there a way to act on the data? So show some text with value1 and another text with value2.

Answer 1

You can retrieve the array from the DE either using an AttributeValue function if the data sits in your sendable DE, or using a Lookup if it's in a related DE. You did not provide any details about your data structure, so I am assuming it's sitting in your sendabe DE like this:

Also I am assuming you want to display this inside an email, so I'd use AMPscript instead of SSJS to pull and parse the data using the BuildRowsetFromString function:

%%[
set @data = AttributeValue("data") /* get data from a sendable DE */

/* build a simple string from array */
set @string = replace(@data, "[", "") 
set @string = replace(@string, "]", "")
set @string = replace(@string, '"', '')

/* build a rowset */
set @rows = BuildRowsetFromString(@string,",")
set @rowCount = rowCount(@rows)

if @rowCount > 0 then

  for @i = 1 to @rowCount do

    set @row = row(@rows, @i) /* get row based on counter */
    set @value = field(@row,1)

    /* conditionally display text based on array data */
    if @value == "value1" then ]%%
    <br>some text for value 1<br>
    %%[ elseif @value == "value2" then ]%%
    <br>some text for value 2<br>
    %%[ elseif @value == "value3" then ]%%
    <br>some text for value 3<br>
    %%[ else ]%%
    <br>fallback text for other values<br>
    %%[ endif

  next @i 
  endif ]%%

I've added some conditions above (%%[ if @value == "value1" then ]%% <br>some text for value 1<br> etc.) to conditionally display text based on the values in the array, but without understanding what your use case is exactly it's hard to point to the best solution.