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
    Nov 3, 2021 at 10:55

1 Answer 1

Reset to default
2

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
    Nov 3, 2021 at 13:27
  • 1
    FOR JSON PATH - thank you for that one!
    – Lukas Lunow
    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
    Nov 3, 2021 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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