Skip to content

Query JSON

Last updated View as MarkdownAgent setup

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

  • Query paths within a stored JSON object - for example, extracting the value of named key or array index directly, which is especially useful with larger JSON objects.
  • Insert and/or replace values within an object or array.
  • Expand the contents of a JSON object or array into multiple rows - for example, for use as part of a WHERE ... IN predicate.
  • Create generated columns that are automatically populated with values from JSON objects you insert.

One of the biggest benefits to parsing JSON within D1 directly is that it can directly reduce the number of round-trips (queries) to your database. It reduces the cases where you have to read a JSON object into your application (1), parse it, and then write it back (2).

This allows you to more precisely query over data and reduce the result set your application needs to additionally parse and filter on.

Types

JSON data is stored as a TEXT column in D1. JSON types follow the same type conversion rules as D1 in general, including:

  • A JSON null is treated as a D1 NULL.
  • A JSON number is treated as an INTEGER or REAL.
  • Booleans are treated as INTEGER values: true as 1 and false as