Gorm: Cannot insert or update JSON Array data to datatypes.JSON column

[jsonbruce]

GORM Playground Link

https://github.com/go-gorm/playground/pull/153

Description

Cannot write JSON Array data to datatypes.JSON column

First defined a table with a datatypes.JSON column, which stores json array data.

type UserWithJSON struct {
    gorm.Model
    Name       string         `json:"name" gorm:"type:varchar(128)"`
    Attributes datatypes.JSON `json:"attributes" gorm:"type:json"`
}

Then do the create or update operation on this table:

jsonData := `{"name": "json-1", "attributes": ["tag1", "tag2"]}`
var jsonMap map[string]interface{}
if err := json.NewDecoder(strings.NewReader(jsonData)).Decode(&jsonMap); err != nil {
    t.Fatal(err)
}
user := &UserWithJSON{}
DB.Where(&UserWithJSON{Name: "json-1"}).Assign(jsonMap).FirstOrCreate(user)


// The below code didn't work either
DB.Where(&UserWithJSON{Name: "json-1"}).
    Assign(map[string]interface{}{"name": "json-1", "attributes": []string{"tag1", "tag2"}}).
    FirstOrCreate(user)


// The below code didn't work either
user := &UserWithJSON{}
DB.Where(&UserWithJSON{Name: "json-1"}).
    Assign(map[string]interface{}{"name": "json-1", "attributes": datatypes.JSON(`["tag3", "tag4"]`)}).
    FirstOrCreate(user)

Insert result:

/playground/main_test.go:27 record not found
[0.061ms] [rows:0] SELECT * FROM `user_with_jsons` WHERE `user_with_jsons`.`name` = "json-1" AND `user_with_jsons`.`deleted_at` IS NULL ORDER BY `user_with_jsons`.`id` LIMIT 1

/playground/main_test.go:27
[0.833ms] [rows:1] INSERT INTO `user_with_jsons` (`created_at`,`updated_at`,`deleted_at`,`name`,`attributes`) VALUES ("2020-09-23 21:15:35.702","2020-09-23 21:15:35.702",NULL,"json-1",NULL)

/playground/main_test.go:30
[0.181ms] [rows:1] SELECT * FROM `user_with_jsons` WHERE `user_with_jsons`.`id` = 4 AND `user_with_jsons`.`deleted_at` IS NULL ORDER BY `user_with_jsons`.`id` LIMIT 1
    main_test.go:34: {"ID":4,"CreatedAt":"2020-09-23T21:15:35.702906+08:00","UpdatedAt":"2020-09-23T21:15:35.702906+08:00","DeletedAt":{"Time":"0001-01-01T00:00:00Z","Valid":false},"name":"json-1","attributes":null}
--- PASS: TestGORM (0.00s)
PASS

Update result:

/playground/main_test.go:27
[0.133ms] [rows:1] SELECT * FROM `user_with_jsons` WHERE `user_with_jsons`.`name` = "json-1" AND `user_with_jsons`.`deleted_at` IS NULL ORDER BY `user_with_jsons`.`id` LIMIT 1

/playground/main_test.go:27
[1.020ms] [rows:1] UPDATE `user_with_jsons` SET `name`="json-1",`updated_at`="2020-09-23 21:17:06.951" WHERE `user_with_jsons`.`name` = "json-1" AND `user_with_jsons`.`deleted_at` IS NULL AND `id` = 4

/playground/main_test.go:30
[0.109ms] [rows:1] SELECT * FROM `user_with_jsons` WHERE `user_with_jsons`.`id` = 4 AND `user_with_jsons`.`deleted_at` IS NULL ORDER BY `user_with_jsons`.`id` LIMIT 1
    main_test.go:34: {"ID":4,"CreatedAt":"2020-09-23T21:15:35.702906+08:00","UpdatedAt":"2020-09-23T21:17:06.951234+08:00","DeletedAt":{"Time":"0001-01-01T00:00:00Z","Valid":false},"name":"json-1","attributes":null}
--- PASS: TestGORM (0.00s)
PAS

Both of the Insert and Update operation didn't parse the datatype.JSON column correctly.

Create directly didn't work either

jsonData := `{"name": "json-1", "attributes": ["tag1", "tag2"]}`
var jsonMap map[string]interface{}
if err := json.NewDecoder(strings.NewReader(jsonData)).Decode(&jsonMap); err != nil {
    t.Fatal(err)
}

DB.Model(&UserWithJSON{}).Create(jsonMap)

Got below error:

/playground/main_test.go:33 row value misused
[0.074ms] [rows:0] INSERT INTO `user_with_jsons` (`attributes`,`name`) VALUES (("tag1","tag2"),"json-1")

Update directly didn't work either

DB.Where(&UserWithJSON{Name: "json-1"}).Updates(jsonMap)

Got below error:

unsupported data type: map[attributes:[tag1 tag2] name:json-1]
[253432.236ms] [rows:0] 

Expected Result

The below 3 methods can create or update the JSON Array data to datatypes.JSON column correctly:

• DB.Where().Assign().FirstOrCreate()
• DB.Model().Create()
• DB.Where().Updates()

[jinzhu]

Fixed the third case, for other cases, maybe you can use json decode to the struct, use it for Assign

user := &UserWithJSON{}
DB.Where(&UserWithJSON{Name: "json-1"}).
    Assign(map[string]interface{}{"name": "json-1", "attributes": datatypes.JSON(`["tag3", "tag4"]`)}).
    FirstOrCreate(user)

Thank you for your report.

[jsonbruce]

Thanks for your solution!!! But I found other issues below @jinzhu

I've tested other cases by using json decode to the struct.

Failed Cases

Updates() method failed to update the JSON Array data to datatypes.JSON column

jsonData = `{"name": "json-2", "attributes": ["tag3", "tag4"]}`
var updatedUser UserWithJSON
if err := json.NewDecoder(strings.NewReader(jsonData)).Decode(&updatedUser); err != nil {
    t.Fatal(err)
}
user := &UserWithJSON{}
DB.Model(&UserWithJSON{}).Where(&UserWithJSON{Name: "json-1"}).Updates(user)

Log below:

[0.590ms] [rows:5] UPDATE `user_with_jsons` SET `updated_at`="2020-09-24 17:23:26.672" WHERE `user_with_jsons`.`name` = "json-1"

It seems the Updates() method cannot parse the datatypes.JSON column.

Successful Cases

Create() insert the JSON Array data to datatypes.JSON column correctly

jsonData := `{"name": "json-1", "attributes": ["tag1", "tag2"]}`
var newUser UserWithJSON
if err := json.NewDecoder(strings.NewReader(jsonData)).Decode(&newUser); err != nil {
    t.Fatal(err)
}
DB.Create(&newUser)