JA EN
shiichan

R2 SQL Now Speaks JSON Functions and EXPLAIN FORMAT JSON!

Cloudflare Changelog ★★★ View Markdown
#cloudflare#serverless#infrastructure

Hey everyone, it's Shiichan! Today I found a nice little update on the data-analytics side, so let me share it with you.

Cloudflare Changelog developers.cloudflare.com

What was announced?

R2 SQL just picked up three new features at once! R2 SQL is Cloudflare's serverless analytics query engine that lets you query Apache Iceberg tables with SQL. With this update, you can now use:

  • JSON functions
  • EXPLAIN FORMAT JSON
  • Queries against unpartitioned tables

I got this from Cloudflare's Changelog.

The story so far

Until now, if you wanted to work with a column full of JSON, you had to pull the data out first and parse the JSON on the client side. On top of that, tables without partitions couldn't be queried directly, which made things a bit awkward for smaller datasets that don't have a natural dimension like time to split on.

What changes

From now on, you can pull JSON values straight out inside SQL! You no longer need to post-process on the client, so your queries get simpler. And since unpartitioned tables can be queried as-is, you don't have to fuss over your design for small datasets anymore.

Dive Deep

With JSON functions, you can extract and check JSON values directly using functions like json_get_str(), json_get_int(), json_get_bool(), and json_contains().

Add FORMAT JSON to EXPLAIN and the query execution plan comes back as structured JSON. That makes it easier to analyze programmatically or wire into your observability setup.

Unpartitioned tables are great for smaller datasets, but the docs note that for tables with more than 1000 files, partitioning is still recommended for better performance. The details live in Limitations and best practices, so give it a look if you handle larger data.

Wrap-up

  • R2 SQL added three things: JSON functions, EXPLAIN FORMAT JSON, and unpartitioned table support
  • You can extract JSON values inside SQL now, cutting down on client-side post-processing
  • EXPLAIN FORMAT JSON lets you treat execution plans as structured data for analysis and observability
  • If a table has more than 1000 files, partitioning is still the recommended way to go

If you analyze Iceberg tables with R2 SQL and have been wrestling with JSON columns, this one's for you!

Related posts