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
– mkopriva, Commented Jul 26, 2019 at 3:50
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 — talkdatatome
– talkdatatome, Commented Jul 26, 2019 at 3:58
An empty '' (single quote string) is invalid json. So if extra is empty you have to provide "some" json, e.g. '{}', or '""'. — mkopriva
– mkopriva, Commented Jul 26, 2019 at 4:03
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, Commented Jul 26, 2019 at 4:13
@mkopriva it worked. I liked the way you played with strings. Cheers !! — talkdatatome
– talkdatatome, Commented Jul 26, 2019 at 4:28

Hayman · Accepted Answer · 2019-07-26 03:05:37Z

2

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.

answered Jul 26, 2019 at 3:05

Hayman

663 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting string into jsonb type postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related