1

I have some data in a data extension that i want to have publicly available in JSON format (so it can be be used on the front-end of other external websites).

Could do Ampscript/SSJS directly on a landing-page/code resource - but i believe this will have a very negative performance impact as it would have to run the server-side code on every request to resource.

What i think would be better was to have a script automation run fx. hourly to output the content and publish this - is this possible?

Improve this question
1
  • How many records do you need to provide in the JSON? You should be OK with building this in a JSON Code Resource without any issues.
    – Lukas Lunow
    Commented Nov 3, 2021 at 10:55

1 Answer 1

Reset to default
3

First of all, make sure this holds NO PII, don't ever publish any PII data.

With that said:

Setup your DE via API with one field "data" and no maxLength (only possible via API) on that. This will hold a JSON.

Create a second field that has a value that you can lookup on, such as "include" and defaultValue "1" (in this example, it's also text)

Then, use an SQL Query Activity and FOR JSON PATH:

SELECT (
    SELECT 
        myNonPersonalData AS [nonPersonalData],
        data2 AS [data2],
        data3 AS [data3]
    FROM
        ENT._Subscribers    
        FOR JSON PATH 
) AS [Data]

Result is one field (data) containing a large json Array that you can just lookup via AMPScript by matching on "include = 1"

put this AMPScript on your cloudpage:

%%[
SET @output = Lookup("myDE","data","include","1")
]%%%%=v(@output)=%%

Publish, done. Depending on the number of records in your planned JSON, do some due diligence in terms of load testing. I have done this for stuff in the range of 500 to 1000 records with a couple seconds runtime of the SQL and no problems in the DE.

Improve this answer
3
  • Makes sense, and thank you for the code examples. The Lookup in ampscript have a minimal performance footprint? The reason is that cloudpage will be loaded with every page-view because it's used frond-end on another external website - thousands per day. ( why my initial though was to have static content on the actual cloud page, refreshed by automation). Actual data is rather small - maybe 100 records.
    – Hyldig
    Commented Nov 3, 2021 at 13:27
  • 1
    FOR JSON PATH - thank you for that one!
    – Lukas Lunow
    Commented Nov 3, 2021 at 13:28
  • At send time, lookups work in the millions within seconds, you should be fine on that front with thousands per day. Also, make your cloudpage a code resource of type JSON, which does not incur supermessage cost.
    – Jonas Lamberty
    Commented Nov 3, 2021 at 15:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .