Inserting string into jsonb type postgres

Question

I have a function that generates a prepared statement for batch insert into postgres where I am trying to insert the string into type jsonb in postgres.

My struct looks like:

type struct1 struct {
id int
comment string
extra string
}

and my table schema looks like:

create table deal (
id bigserial,
comment varchar(75),
extra jsonb
)

and I want to dump []struct1 to Postgres DB "deal".

My function which generates the prepared statement looks like this:

func BulkInsert(str []struct1, ctx context.Context) string {
    log.Debug("inserting records to DB")
    query := fmt.Sprintf(`insert into deal (%s) values `, strings.Join(dbFields, ","))
    var numFields = len(dbFields)
    var values []interface{}
    for i, database := range str {
        values = append(values, database.Comment,`'`+database.Extra+`'`)
        n := i * numFields
        query += `(`
        for j := 0; j < numFields; j++ {
            query += `$` + strconv.Itoa(n+j+1) + `,`
        }
        query = query[:len(query)-1] + `),`
    }
    query = query[:len(query)-1]
        return query

Expected results should be: I should be able to insert string to json or you can say cast string to json and dump it.

The actual result is : could not save batch: pq: invalid input syntax for type json"

Have you tried `'`+database.Extra+`'::jsonb`? And does database.Extra hold valid json format? — mkopriva
Yes but database.Extra can be optional so I might get it sometimes and might not. And '`+database.Extra+`'::jsonb do not work I tried — rvl007
An empty '' (single quote string) is invalid json. So if extra is empty you have to provide "some" json, e.g. '{}', or '""'. — mkopriva
Also, my first comment is wrong. When needed, the ::jsonb should be appended to the parameter reference ($2::jsonb) and not the parameter itself. — mkopriva
@mkopriva it worked. I liked the way you played with strings. Cheers !! — rvl007

Hayman Hayman · Accepted Answer · 2019-07-26T03:05:37

Function of json_build_array('exp1'::Text, 'exp2'::Text) may help you.

return json object: {'exp1', 'exp2'}

And extract the values just use operator ->><index> like ->>1 to get 'exp2'.

If you just want to insert into database, function of to_json('any element') should also works, which can convert any element to a json object.

And you can get more funtions about json(jsonb) in postgres document.

Inserting string into jsonb type postgres

1 Answers