{} MCE SQL Reference doesn't mention JSON at all. @* Marketing Cloud Engagement * SQL reads, changes and builds it anyway. Learn how: https://mateuszdabrowski.pl/docs/salesforce/marketing-cloud-engagement/sql/sql-json-functions/
API payloads, CloudPages submissions and product feeds land in your Data Extensions as JSON. Most of us reach for SSJS to read them and fight against performance, but a Query Activity can do much of that work itself, with a few traps along the way:
π΅ It builds JSON, too.
Despite documentation, the silently updated SQL Server behind MCE supports 2022 feature set: JSON_OBJECT, JSON_ARRAY and JSON_PATH_EXISTS. FOR JSON PATH turns each subscriber's abandoned cart into one field your email can loop over.
π Reading has a limit.
JSON_VALUE returns NULL for any value over 4000 characters. 4000 works, 4001 doesn't, and there's no error. OPENJSON would lift the limit, but the MCE syntax validation treats it as a Data Extension name (sic!).
π΄ Broken JSON stops everything.
One truncated payload fails the whole Query Activity, and the Action Log says only "Automation failed due to system error." Wrap your reads in ISJSON, and the broken records return NULL instead.
Every output in the article comes from a live test in my Query Studio replacement - SQL Studio - which just got version 1.2: save results straight to a new Data Extension, share snippets across your Business Unit and add a JOIN to your query in one click.
More to come, so keep on querying!
