Skip to main content

JSON Columns

Store and query JSON data directly in your SQL tables. JSON columns are useful for flexible, semi-structured data that doesn't fit a rigid schema.

Feature Support Matrix​

FeaturePostgreSQLMySQL/MariaDBSQLite
Basic JSON equality (whereJson)✓✓✓
Nested property match✓✓~
Array element match✓✓~
AND/OR JSON conditions✓✓~
JSON containment (whereJsonContains)✓✓✗
JSON IN/NOT IN✓✓✓
Select JSON column✓✓✓

~ = Limited/partial support, see SQLite JSON Limitations

Basic JSON Filtering​

Query for full JSON object equality or by nested properties.

// Full object equality
await User.query().whereJson("json", { foo: "bar", arr: [1, 2, 3] }).first();

// Nested property (works best in PostgreSQL/MySQL)
await User.query().whereJson("json", { profile: { info: { age: 42 } } } ).first();

Logical Operations (AND/OR)​

Combine multiple JSON conditions using andWhereJson and orWhereJson.

// AND combination
await User.query()
  .whereJson("json", { logic: "A" })
  .andWhereJson("json", { status: "active" })
  .first();

// OR combination
await User.query()
  .whereJson("json", { logic: "A" })
  .orWhereJson("json", { logic: "B" })
  .many();

Note: Complex AND/OR combinations are only fully supported in PostgreSQL/MySQL. SQLite has limited support. See SQLite JSON Limitations.

Array and Object Filtering​

Query by array elements or nested object properties in JSON columns.

// Array element match (best in PostgreSQL/MySQL)
await User.query().whereJson("json", { tags: ["frontend", "typescript"] }).first();

// Nested object property
await User.query().whereJson("json", { user: { profile: { settings: { theme: "dark" } } } }).first();

Data Variations​

Insert and retrieve various JSON structures, including primitives and null.

const variants = [
  { foo: "bar", arr: [1, 2, 3], nested: { a: 1 } },
  { simple: "string value" },
  { number: 12345 },
  { bool: true },
  null,
];
for (const json of variants) {
  await User.insert({ ...UserFactory.getCommonUserData(), json });
}

Bulk Operations​

Insert many users with different JSON values and query using whereJson or whereJsonIn.

await User.insertMany([
  { ...UserFactory.getCommonUserData(), json: { bulk: 1 } },
  { ...UserFactory.getCommonUserData(), json: { bulk: 2 } },
]);

await User.query().whereJson("json", { bulk: 1 }).first();
await User.query().whereJsonIn("json", [{ bulk: 1 }, { bulk: 2 }]).many();

Advanced JSON Filters​

Use containment and negation for advanced filtering (PostgreSQL/MySQL only).

// Containment (not supported in SQLite)
await User.query().whereJsonContains("json", { arr: [1, 2, 3] }).first();
await User.query().whereJsonNotContains("json", { arr: [1, 2, 3] }).first();

// Negation
await User.query().whereNotJson("json", { foo: "bar" }).first();

Selecting JSON Columns​

Select only the JSON column or combine with other columns.

// Select only JSON
await User.query().select("json").where("email", "=", user.email).first();

// Select JSON and another column
await User.query().select("json", "email").where("email", "=", user.email).first();

Cross-Database Notes​

  • PostgreSQL/MySQL/MariaDB: Full support for advanced JSON queries, containment, and deep property matching.
  • SQLite: Only supports basic equality and simple queries. Advanced features (containment, deep property, array matching) are limited or unsupported. See SQLite JSON Limitations for details.

See also: