JSON Support

quill uses one syntax for JSON paths, column->a->b[0], and renders the right SQL for each dialect.

On this page: Reading JSON · JSON conditions · Updating JSON paths · Per-dialect behaviour

Reading JSON

Use -> in selects, wheres and ordering:

Select("options->theme")               // "options"->>'theme'     (PostgreSQL)
Where("options->theme", "dark")
Where("options->enabled", true)        // compares as a JSON boolean
OrderBy("options->rank")
Select("meta->tags[0]")                // array index

Single quotes in a path are escaped, so a path cannot break out of its SQL literal.

JSON conditions

WhereJsonContains("options->tags", "go")
WhereJsonDoesntContain("options->tags", "go")
WhereJsonContainsKey("options->theme")
WhereJsonDoesntContainKey("options->theme")
WhereJsonLength("options->tags", ">", 2)
WhereJsonOverlaps("options->tags", []string{"a", "b"})   // MySQL and MariaDB only
WhereJsonDoesntOverlap("options->tags", []string{"a"})

Every WhereJson* method has an Or variant (OrWhereJsonContains, ...).

Updating JSON paths

Use column->path as the key in Set:

conn.Update("users").
    Set("options->theme", "dark").
    Set("options->tags[0]", "go").
    Set("name", "Ann").
    Where("id", 1).
    Exec(ctx)

Dialect	SQL
PostgreSQL	"options" = JSONB_SET("options"::JSONB, '{"theme"}', $1) (values are JSON-encoded)
MySQL	`options` = JSON_SET(`options`, '$."theme"', ?) (booleans inlined; maps and slices bound as CAST(? AS JSON))
MariaDB	Like MySQL, but maps and slices use JSON_QUERY(?, '$') because MariaDB has no CAST AS JSON
SQLite	"options" = JSON_SET(IFNULL("options", JSON('{}')), '$."theme"', JSON(?))

Rules:

• nil becomes JSON null (the key stays).
• A map or slice replaces whatever was at the path. It does not merge.
• Several paths of the same column become one assignment (nested JSONB_SET on PostgreSQL, a multi-pair JSON_SET on MySQL and SQLite). PostgreSQL rejects assigning a column twice.
• On PostgreSQL, MySQL and MariaDB only the last key is created. Write options->a->b only if options->a already exists. SQLite 3.53.4 also creates missing parents; that is not verified on older SQLite, so do not rely on it.
• SQLite does not support db.Raw(...) as a JSON value and returns ErrUnsupported.

Per-dialect behaviour

	PostgreSQL	MySQL	SQLite
Text selector	"c"->'a'->>'b'	JSON_UNQUOTE(JSON_EXTRACT(`c`, '$."a"."b"'))	JSON_EXTRACT("c", '$."a"."b"')
WhereJsonContains	("c"->'a')::JSONB @> ?	JSON_CONTAINS(c, ?, '$."a"')	EXISTS (SELECT 1 FROM JSON_EACH(...) WHERE value IS ?)
WhereJsonContainsKey	COALESCE((c)::JSONB ? 'k', FALSE)	JSON_CONTAINS_PATH(...)	JSON_TYPE(...) IS NOT NULL
WhereJsonLength	JSONB_ARRAY_LENGTH(...)	JSON_LENGTH(...)	JSON_ARRAY_LENGTH(...)
WhereJsonOverlaps	ErrUnsupported	JSON_OVERLAPS(...)	ErrUnsupported

Differences you can observe:

• Missing path in WhereJsonDoesntContain: on PostgreSQL and MySQL a missing path makes the test NULL, so the row is excluded. On SQLite and MariaDB it is true, so the row is included.
• Containment values are JSON-encoded and bound on PostgreSQL and MySQL. SQLite compares the raw value.
• PostgreSQL boolean test needs a jsonb cast, which quill adds for you.
• MariaDB text selectors deliberately keep the JSON_UNQUOTE(JSON_EXTRACT(...)) form, because json_value would return NULL for arrays and objects.

More in Dialect Differences.