CAML vs REST vs JSON: The Real Power Play

Quick question: When was the last time you wondered if you’re
using the best tool for querying or formatting your Microsoft
Lists? If your answer is ‘every time I hit a performance
bottleneck,’ you’re definitely not alone. Today, we break down
the real pros and cons of CAML, REST, and JSON formatting that
every Power Platform professional should know before choosing a
data strategy. Spoiler: The most obvious choice might not be the
best one.


Why CAML Refuses to Die: The Hidden Power of Old-School Queries


Let’s be honest—CAML isn’t winning any popularity contests. But
if you’ve ever seen someone suggest tossing it out because “REST
does everything now,” there’s a good chance they haven’t run up
against the specific corners of Microsoft Lists that still make
CAML feel like the only grownup in the room. It’s like seeing a
retired engineer hanging around the factory floor, outlasting
every new hire. Most pros—especially folks new to Power Platform
or SharePoint—just assume REST is supposed to be the replacement
for everything legacy. In presentations, REST arrives dressed in
a cloud-friendly suit. Documentation after 2018 barely mentions
CAML except in a footnote. Teams default to REST endpoints. Power
Automate connectors wave the REST API flag. Ask around in most
Microsoft 365 circles, and you’ll probably hear that “nobody uses
CAML anymore.”But then, quietly, the old XML-based ranger pops up
right where you need that one thing REST can barely do. We’ve all
hit that point: You open a list, the UI is modern, the Teams app
syncs fine, and then you try to pull a filtered report with
multiple levels of AND/OR logic. You stack nested lookups, date
filters, and a couple quirky calculated columns. Suddenly, REST
throws up its hands and gives you either a generic timeout or an
error that sends you down two dozen GitHub threads. Try
expressing a query with eight different filters and some
child-parent relationships. REST will let you write it, sure, but
the JSON gets ugly fast—and performance drops like a rock.Not
long ago, I watched a Power Platform team migrate an old
SharePoint workflow—years of business logic packed into classic
designer flows—over to REST-based flows in Power Automate. On
paper, it looked simple: swap out CAML for REST queries, update
connections, and call it a modernization win. But on day one of
testing, the migration choked on a batch operation pulling data
from a list containing 75,000 items. With CAML, the old flow
sliced off results in neat little pages, kicked out only the
columns it needed, filtered everything server-side, and shipped
back exactly what the automation required. The REST-based
version, running nearly identical logic, ballooned in both
response size and query time. And just for fun, it blasted past
the 5,000-item list view threshold, which is always a favorite
trouble ticket. Tech support spent a week flipping through REST
documentation, only to end up re-enabling the CAML-based query
for those edge cases.Here’s what stands out about CAML: the way
it lets you build queries with complex grouping and advanced
filtering that REST, for all its flexibility, still finds clunky.
CAML’s structure isn’t just XML for the sake of pain—it actually
shapes the query so it can be parsed, executed, and optimized
server-side. That’s code running in the SharePoint backend, not
your browser or Power Automate client pulling tens of megabytes
over the network. When you need to return only the rows that hit
three different lookup values, don’t bring along every item in
the list, and avoid blowing past thresholds, CAML can quietly
make that happen. Try this—auto-approve a set of requests based
on five nested business rules, filtering for calculated fields
and workflow status, all without loading every entry. Most
REST-based setups either throw you back more data than you need
or require running extra filtering logic after-the-fact, burning
up memory, flow time, and patience.There’s even a well-known
backend story in the SharePoint world about massive lists—the
real beasts, with hundreds of thousands of rows—where
administrators had to script administrative reports. The rumors
say that when REST API built the same report, it spun for
minutes. Switch back to CAML, and suddenly the data spilled out
in seconds. The magic? CAML’s XML is tailor-made for SharePoint’s
indexing engine. The query goes straight into crawled indexes,
skips rows that don’t fit, and doesn’t waste time serializing the
entire dataset in JSON for the client to filter.It’s not just
urban legend or stubborn admins refusing to learn new tricks,
either. Microsoft’s own documentation—usually quick to point out
“modern” options—still quietly signals that CAML queries are the
answer for certain types of intricate filters. Community threads
from the last year have people swapping code samples for using
CAML inside custom PowerShell scripts or complex approval
workflows because it simply outperforms the alternatives in more
than a few situations.So is betting on CAML smart, or are you
just risking future headaches clinging to something scheduled for
demolition? The real answer is sneakier: CAML isn’t dead weight,
and when you’re staring down advanced filters or those thorny
performance edge cases, it consistently pulls its weight,
sometimes better than anything REST can offer. You might even
call it a secret weapon reserved for those frustrating moments
when modern tools suddenly lose their shine.Which raises a bigger
question. With so much power hiding in CAML, why does every
vendor, every Microsoft roadmap, and most Power Platform
solutions still keep steering us toward REST APIs? If the old way
works so well behind the scenes, what’s really driving the shift?
Let’s see what’s waiting on the other side.


REST APIs: The Double-Edged Sword of Modern List Access


Let’s talk REST APIs—the darling of modern development in the
Microsoft Lists world. It’s hard to miss how every new
integration, extension, or Power Automate connector leans into
REST. This is the playbook: Microsoft wants tools to work across
the cloud, in browsers, with Teams, and on mobile, so the REST
endpoints are everywhere you look. Need to plug your list data
straight into Power BI for a quick dashboard? REST endpoint. Got
a third-party tool promising smarter notifications or advanced
reports? Odds are, they’re hitting the REST API as their gateway
into the data layer. Even for developers, REST feels like a
friendly handshake—everything works in HTTP, the docs are packed
with JSON samples, and most programming environments already know
how to speak the language. There’s a reason third parties default
here. REST works almost everywhere with minimal fuss. Whether
you’re connecting old-school SharePoint sites or shiny new
Microsoft Lists spun up through the cloud, the REST API surface
is broad enough to cover most use cases. From a cloud readiness
standpoint, it’s gold. No obscure XML to chase, just
well-documented endpoints and parameters, ready to slot into
low-code, pro-code, or even a quick script someone hacks together
before a meeting. Modern app architecture almost demands this
kind of flexibility, and Microsoft’s whole SaaS ecosystem is
built to support it.But under that surface, things get
complicated fast. REST’s catch-all approach makes it easy for
first-timers to ignore some real pain points until it’s too late.
One of the first headaches is API throttling. REST’s openness
means that, when your solution gets busy—or if a connector or
script starts firing off hundreds of requests in a short
window—SharePoint or Microsoft Lists throttle your traffic. It’s
not always obvious when it’s happening, either. Sometimes, you
get a helpful “429” error. Other times, queries just slow down or
silently fail, stalling automations and user actions. Now add in
service limits—thresholds on how much data you can return, how
many items per call, and max concurrent requests—and you realize
REST gives you flexibility but not always predictability.That
scales right into performance during bulk data operations. In
testing, REST feels slick. Spin up a list, send a query or two,
it works. But production is a different animal. Picture a
workflow that breezes through functional testing—200 items in a
dev list, everything smooth. Then the thing goes live, and the
real production list has 40,000 records. Overnight, Power
Automate starts spitting out failures, flows get stuck retries,
and the support mailbox lights up. The team quickly traces the
failures to throttled REST calls. It’s not that your script was
wrong; it’s just that REST doesn’t handle sudden volume spikes
with much grace, especially when someone is looping through
responses or chunking up write operations. Even a simple “bulk
update” script turns unruly when each call takes longer and the
system shoves back with a throttle warning.REST’s writing
operations, too, can become a labyrinth when scale rears its
head. Sending a few updates that pass in a basic JSON payload
hits no snags, but batch writes? Multiple item updates using REST
need careful orchestration—multiple requests, careful attention
to concurrency, and error handling that can retry once throttling
kicks in. CAML, for all its syntax woes, let you slice and dice
server-side, submit a query built for SharePoint’s internal
engine, and get exactly the result set you wanted with less
client effort. With REST, you’re often left piecing together
results on the client side, combining partial responses, and
spending more time marshalling data in browser memory or inside
cloud flows.That’s the real tradeoff here: REST shifts more
processing to the client. So now, instead of SharePoint quietly
handling filtering, paging, or joining list data server-side,
you’re sending a raw list of items back to your automation or
script, then filtering, joining, or formatting after the fact.
For some solutions, that’s fine—especially when you want to tap
into Power Automate, Teams, or even third-party connectors that
only understand the REST interface. In fact, for bringing data
into a Teams tab or pulling quick updates into an app, REST is
what makes it all possible. It’s also WHY you almost never see
native connectors or prebuilt integrations using CAML—it would
mean rebuilding that server-side parsing logic everywhere.But
that gain in versatility has sharp edges. REST works well for
breadth—lots of integrations, lots of basic CRUD operations—but
its reliability and efficiency drop as your use case grows in
complexity or scale. Experienced admins and architects know that
when you hit throttling, or permissions cave in due to badly
configured app registrations, your automations can break without
warning and the root cause isn’t always obvious. Troubleshooting
becomes a process of sifting through logs, watching retry
behavior, and praying the next release doesn’t tighten limits
even further.So, is REST the modern hero it pretends to be, or
just a handy shortcut with some hidden tradeoffs? From where we
sit, REST is the default because it’s easy and everywhere, not
always because it’s best for every scenario. Lean on REST, but
don’t let its convenience fool you into skipping a scale test or
missing how much work you’re dumping on the client. In the wrong
situation, REST is a recipe for performance surprises, failures
at scale, and headaches for whoever maintains your flows once
you’re off to the next project.But if both CAML and REST have
these uncomfortable blind spots, it begs the question: Where does
JSON formatting fit? For many teams, it looks like pure UI sugar,
but things beneath the surface can get trickier than the format’s
simplicity suggests.


JSON Formatting: Custom UI or Unseen Trap?


JSON formatting is that feature everyone shows off at Microsoft
Lists demos—take a plain list, sprinkle in some curly braces, and
suddenly you’ve got a dashboard with bold colors, fancy icons,
and even clickable buttons. All of this, and you never have to
open Visual Studio or write a single backend flow. It’s the kind
of thing business users and power users both love, because it
promises instant results—no waiting on a dev team, no approval
for custom code. Change a line, hit save, and the list view
transforms before your eyes.For a lot of organizations, this
feels like magic. You want to highlight overdue tasks? A few
lines of JSON and overdue items glow red. Need to add a progress
bar to every project row? That’s a web search and a quick
copy-paste away. Power Platform folks can build out what look
like mini-apps—complete with conditional formatting, tooltips,
and icons to nudge users in the right direction, all without ever
writing a full-blown application. You end up with a workspace
that’s actually pleasant to use. No more pleading with admins for
a custom SharePoint solution or trying to wrangle a third-party
UI add-in. It’s hard not to like a tool that promises results
before lunch.But the shine wears off once you try to push the
format past its comfort zone. Here’s where the friction starts:
complex conditional logic might sound easy enough, but once
you’ve got six different states, nested if-elses, and a few
calculations, the JSON gets unreadable. Maintenance becomes a
real headache, especially when nobody documents what all those
lines actually mean. Tweaking a color or updating a condition
forces you to hunt through hundreds of brackets, just hoping you
don’t break the whole thing. If you’ve ever inherited a list
where someone “just made the formatting prettier,” you know how
quickly it spirals into copy-paste spaghetti.Performance is
another silent killer. With smaller lists, the browser barely
notices, and everything feels instant. Scale things up—say, that
same list hits 10,000 items—and suddenly, nobody is smiling. I
worked with a finance team that built a fantastic dashboard,
tracking status and targets for thousands of projects. The UI was
slick in testing, but after a year, when the item count shot up,
users started complaining about their browsers crawling. The
culprit wasn’t the list or the network—it was the JSON view
formatting, layering extra DOM updates and client rendering on
every item. The browser had to process every conditional format,
every icon, every change, as the list loaded. A few seconds here
turns into a slog when multiplied across thousands of records.
Heavy JSON customization, especially when piling on nested logic
or media, builds up page weight fast. Microsoft doesn’t publish
hard limits, but the experience makes it obvious. You might not
see outright failures, but users notice every lag.All of this
ties back to where JSON runs—squarely in the browser. There’s no
server-side processing when you add or tweak formatting. Every
calculation, every color choice, every visual cue happens on the
client. That means network speed, browser performance, and even
device age become factors in how usable your list actually feels.
Compare that to CAML handling advanced filters or REST moving
data in bulk—those operations use Microsoft’s backend strength.
JSON formatting, though, bets everything on the user’s device,
and there’s zero “offload” when the configuration gets
ambitious.And it’s not just performance worries. JSON formatting
is still a moving target. Microsoft keeps tweaking property
support, sometimes launching new view features quietly and
occasionally making changes that break “creative” formatting
solutions. Property support isn’t fully documented, so you might
discover a trick that works perfectly today, only for it to stop
after the next platform update. That’s one reason sysadmins tend
to distrust lists that rely on heavy formatting for key
processes—something as simple as a field change can unravel
months of UI tweaks.On security, JSON formatting is a strange
beast. Since it operates client-side, it only exposes data
already permitted by the current user’s access. You can’t
magically surface restricted columns—the underlying data model
enforces those rules. Compared to REST, which can be called from
other apps or exposed via open endpoints, or CAML which is parsed
on the server, JSON formatting is less risky for leaks but can
still reveal more data if admins slap on formatting against
columns users shouldn’t really see in a certain view. There’s
also the temptation to “hide” data with view tricks, when what
really needs fixing are the permissions behind the scenes.That
said, real-world lists often need fast solutions. Maybe the
business wants a high-priority board with colored categories and
quick links, and nobody’s signing off on a week-long custom dev
sprint. JSON formatting is perfect for quick wins, even if it’s
not the answer forever. There are scenarios—quick status boards,
team dashboards, one-off event lists—where the ability to shape
the UI in minutes beats concerns over long-term
maintainability.So, you get power, but at a price. JSON
formatting can create beautiful, functional dashboards in no
time, but it’s not the answer for lists that need to scale or
stand the test of SharePoint’s shifting feature set. If you’ve
ever wondered, when should you reach for CAML, REST, or JSON? It
all comes down to understanding how each tool matches your actual
needs, especially once everyone has moved on to the next
must-have app.


Choosing Wisely: The Real Impact of Your Data Source Decision


If you’ve ever raced to finish a Power Automate project on a
tight deadline, chances are you chose whichever method felt
fastest—maybe REST because it’s what everybody’s using, or JSON
formatting because the customer wanted a quick UI facelift. It’s
how most real-world Microsoft Lists solutions get built. No team
schedules an architectural summit just to pick between CAML,
REST, or JSON. There’s pressure, there’s a backlog, someone wants
results, and the default wins. Living by the “just ship it”
mindset saves time at the start. The trouble is, you don’t always
see the impact until something fails under load, a compliance
check kicks in, or that simple enhancement request demands a full
rewrite. The stakes start to show up after the fact, when
operations slow to a crawl, an integration breaks, or data lands
in the wrong hands.The shortcut, of course, is tempting. You add
a JSON view to dress up a list, or you hook up Power Automate
with REST for instant flows. Nobody is hunting for gotchas when a
fix needs to deploy “by Friday.” But what gets missed are the
real risks lurking behind the easy wins—breaking changes as
Microsoft moves the goalposts, server load that quietly breaks
thresholds, or security gaps nobody sees coming. I’ve seen
security audits catch sensitive columns leaking through a hasty
JSON view left over from testing. In one case, a team added
column formatting to make it easy for managers to spotlight
“private” feedback fields—fields that, if you knew the right URL
trick, revealed more than they realized. A few months later,
compliance flagged it in a periodic access review because the
formatting surfaced metadata that users weren’t supposed to see
together. Not a one-off, either; the risk is real, especially
when view logic changes faster than permissions get reviewed.REST
brings its own flavor of trouble. In theory, it’s all automated,
repeatable, and built for scale. But it also means you’re now at
the mercy of throttling, evolving API behaviors, and just plain
silent failures. I watched a retail deployment collapse after a
busy holiday for a very simple reason—REST calls that failed due
to throttling, but only when the production list got big. In test
environments, there’s plenty of breathing room, requests glide
through. Go live, spike the volume, and REST quietly drops
updates or starts retrying in the background. The flow looked
fine—until you drilled into logs and found items stuck in a
processing loop, simply because the API was protecting itself.
From a business point of view, this looked like operations
falling behind. From a tech perspective, nobody wants a solution
where the only symptom of failure is a missed SLA and a queue
full of “successful” but unprocessed items.Then there’s the joy
of maintainability—or, more often, the lack of it. CAML, if
you’ve ever read an advanced query, looks like a history lesson
in verbose XML. The syntax is detailed, and the documentation
reads like a puzzle. Try giving a complex CAML snippet to someone
new on the team, and watch how long it takes before they ask if
there’s an easier way. On the flip side, REST endpoints sprawl
out fast. A single solution might call half a dozen different
endpoints, with each flow, each connector, and each automation
owning a little piece of the API landscape. If you need to
refactor or unlock a new scenario, you’re tracing requests and
permissions across multiple moving parts. JSON? JSON brings its
own brand of chaos. Formatting rules stack on formatting rules.
After a few months, the pretty board becomes a minefield of
conditional logic you can’t untangle without breaking features.
It’s readable if you wrote it. Otherwise, it’s just
noise.Security is the other elephant in the room. CAML lives on
the server side and only returns what the query allows. REST APIs
go through their own permission checks, but endpoint sprawl means
mistakes can slip in—suddenly, a script exposes data in another
system. JSON formatting leans on whatever the current user can
see, but masking data with formatting isn’t the same as truly
restricting it. And the underlying permissions model, especially
in SharePoint-backed lists, can drift when lists grow or columns
repurpose. Keeping up with what’s exposed and where is technical
debt waiting to multiply.Performance, too, demands careful
thought. Large, complex CAML queries can crush a server if
misconfigured but can also save bandwidth by doing all the heavy
lifting in the backend. REST, with all its flexibility, throws
most of the burden onto network traffic and the client. It’s
perfect for micro-services and cloud connectors, but at scale,
the payload ballooning tends to punish anyone sitting behind a
slow connection. JSON formatting, meanwhile, lives and dies by
the browser: small lists fly, big lists grind. That’s before you
start counting the cost of heavy formatting and client-side
rendering.Every method asks you to weigh those risks. Do you want
maintainability, or raw speed? Are you building something quick
to solve today’s problem, or is this solution likely to be
here—and need support—twelve months from now? No single approach
is perfect, and Microsoft’s own guidance boils down to matching
the tool to the task. CAML in server-heavy querying, REST when
interoperability matters, JSON for rapid UI tweaks—each has a
sweet spot if you’re willing to accept the trade-offs.Next time
you’re starting a Lists project, ask yourself who’s using it, how
long it needs to last, and whether you’ll have to defend it in a
security review. The best architecture is the one that avoids
surprises six months after you’ve lost interest and moved on.


Conclusion


If you’ve worked with Microsoft Lists, you know the most obvious
choice isn’t always right. The quick answer today can become a
headache down the line, especially when team size, compliance, or
business needs shift. Before launching your next solution, take a
step back and ask yourself—not just if it works, but if it lasts,
protects your data, and makes sense six months from now. I’d love
to hear your stories too. Drop your strangest CAML queries, REST
frustrations, or creative JSON hacks down below. Someone’s
oddball workaround today could be another admin’s lifesaver
tomorrow.


Get full access to M365 Show - Microsoft 365 Digital Workplace
Daily at m365.show/subscribe