Support for JSONB?

[StephanSchmidt]

Is there support to specify how to convert JSONB data to Go structs? I've searched the documentation but haven't found anything.

[jschaf]

Interesting. Do you mean something like:

-- name: FetchJsonExample :one
SELECT 1 as col_one, '{"foo": "bar"}'::jsonb as json_col

Would generate this Go code:

type JsonColType struct {
    Foo string
}

type FetchJsonExampleRow struct {
    ColOne int
    JsonCol JsonColType
}

If that's the case, then no, pggen can't do anything like that. pggen converts Postgres types to Go types. In this example, sonb is the Postgres type and pggen converts it to the Go type pgtype.JSONB (overridable to []byte or string).

My recommendation is to extract the desired fields in the SQL query. Postgres has rich support for manipulating jsonb objects and arrays.

-- name: FetchJsonExample :one
SELECT  '{"foo": "bar"}'::jsonb ->> 'foo' as json_col

A more involved alternative is to create a composite type in Postgres create the composite type in the query from the jsonb column.

-- schema.sql
CREATE TYPE json_col_type AS ( foo text );

-- query.sql
-- name: FetchJsonExample :one
SELECT 1 as col_one, ('{"foo": "bar"}'::jsonb ->> 'foo')::json_col_type as json_col

There's more example of composite types in https://github.com/jschaf/pggen/tree/main/example/composite.

It's unlikely I'll add JSON unmarshal support directly into pggen. The reasons are:

• If it can be done in SQL, I'd prefer to keep it there as it keeps pggen relatively simple.
• It would require some way of annotating the output type in addition to the go-type flag.

[StephanSchmidt]

Thanks for the long answer.

I would prefer unmarshalling. Wouldn't adding go-type support in the way of table.field=gotype work? With pgx supporting Jsonb with Valueer and Scanner interfaces out of the box (so no unmarshalling code in pggen needed)?

The other solutions seem to contradict the reasons you would save Json into the database in the first place. I could take a look if I can make it work.

[kirk-anchor]

You can unmarshal in Postgres. For example, if you have a table

CREATE TABLE foo (
    data jsonb
);

Create a TYPE in Postgres for your Go struct with whatever fields you want to unmarshal

CREATE TYPE foo_data AS (
    id text,
    owner_id text
);

Then parse the JSON column in your query

-- name: GetFoo :one
SELECT
    jsonb_populate_record(NULL::foo_data, data)
FROM
    foo;

This will generate func (q *DBQuerier) GetFoo(ctx context.Context) (FooData, error) and Go struct

// FooData represents the Postgres composite type "foo_data".
type FooData struct {
    ID      *string `json:"id"`
    OwnerID *string `json:"owner_id"`
}

To use the struct as an input arg, marshal it in the query SELECT to_jsonb(pggen.arg('foo_data')::foo_data)