JSON objects in SQLite are returned as strings

Version

1.27.0

What happened?

A json_object() function that returns a JSON-formatted object is scanned as a string instead of []byte or json.RawMessage. The return type for a :many query that does SELECT json_object() is []interface{}, while each item in the slice is of type string, therefore, when rendered as JSON, becomes an escaped JSON string. I've tried manually updating the generated code to cast strings to json.RawMessage and it worked as expected. Configuring an override of a json DB type to json.RawMessage results in "0" being returned. Configuring an override for jsonb instead results in some base64-encoded string.

I cannot find a workaround for this at the moment (apart from modifying the auto-generated code) but I think that json_object() and jsonb_object() (perhaps json_array() as well) expressions should be compiled as json.RawMessage.

Relevant log output

items, err := queries.New(db).Foo(context.Background())
if err != nil { panic(err) }
enc := json.NewEncoder(os.Stdout)
enc.SetIndent("", "  ")
enc.Encode(outbox)

// stdout:
// [
//   "{\"id\":3,\"value\":\"baz\"}",
//   "{\"id\":2,\"value\":\"bar\"}",
//   "{\"id\":1,\"value\":\"foo\"}"
// ]

Database schema

CREATE TABLE foo (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    value TEXT NOT NULL
);

SQL queries

SELECT json_object('id', id, 'value', value)
FROM foo
ORDER BY id DESC;

Configuration

version: '2'
sql:
  - engine: 'sqlite'
    schema: db/schema.sql
    queries: db/queries
    gen:
      go:
        package: 'queries'
        out: 'queries'
        emit_result_struct_pointers: true
        emit_params_struct_pointers: true

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

SQLite

sqlc-dev / sqlc