Does upsert work with postgres :json columns?

[aj0strow]

Hey tried to batch upsert documents with json fields. I get an error for PgHstore so I'm guessing it thinks the hashes are supposed to be :hstore and not :json.

NameError: uninitialized constant PgHstore
    ~/.rbenv/versions/2.2.0/lib/ruby/gems/2.2.0/gems/upsert-2.1.0/lib/upsert/connection/postgresql.rb:18:in `bind_value'

Is there a setting to have it assume :json instead?

[johnrees]

I just ran into this too, when trying to update a jsonb column. Got to head out now but will try and dig further later.

when Hash
  # you must require 'pg_hstore' from the 'pg-hstore' gem yourself
  ::PgHstore.dump v, true
else
  super
end

[randomm]

Did you guys have any luck with this? I'm also struggling with trying to upsert JSON.

[johnrees]

I took a totally different approach in the end but I'd also be interested to know if anyone had come up with anything.

[randomm]

I did some digging around and indeed it confuses json for Hash in the Postgreslq connection: v.class returns Hash for json data.

Edit: ha, I'm moving stuff between databases, and what is stored in a json field in database A, when taken out via ActiveRecord IS an instance of Hash. So for me the simple solution is to call .to_json on it when inserting into the other db. Not very helpful sorry...

[maciejkowalski]

@randomm

Even to_json doesn't seems to work :/

PG::UndefinedFunction - ERROR:  operator does not exist: json = json
LINE 2: ...onversion_goal_id_sel" IS NULL)) AND ("crop_data" = "crop_da...

[maciejkowalski]

This gem has very slow perfomance for me...

Snippets from rails logs

#PLAIN
Completed 200 OK in 3644ms (Views: 0.9ms | ActiveRecord: 1173.6ms)
Oink Action: api/pages#update
Memory usage: 1772200 | PID: 24129
Instantiation Breakdown: Total: 222 | Widget: 132 | ImageWidgetAssoc: 42 | Image: 42 | Page: 2 | User: 1 | Role: 1 | Account: 1 | PageVariant: 1

#AR transaction
Completed 200 OK in 2428ms (Views: 2.9ms | ActiveRecord: 594.3ms)
Oink Action: api/pages#update
Memory usage: 1772200 | PID: 24129
Instantiation Breakdown: Total: 222 | Widget: 132 | ImageWidgetAssoc: 42 | Image: 42 | Page: 2 | User: 1 | Role: 1 | Account: 1 | PageVariant: 1

#UPSERT GEM
Completed 200 OK in 4062ms (Views: 0.3ms | ActiveRecord: 598.8ms)
Oink Action: api/pages#update
Memory usage: 1214764 | PID: 25274
Instantiation Breakdown: Total: 222 | Widget: 132 | ImageWidgetAssoc: 42 | Image: 42 | Page: 2 | User: 1 | Role: 1 | Account: 1 | PageVariant: 1

# UPSERT GEM + AR transaction
Completed 200 OK in 4136ms (Views: 5.1ms | ActiveRecord: 901.3ms)
Oink Action: api/pages#update
Memory usage: 1214764 | PID: 25274
Instantiation Breakdown: Total: 222 | Widget: 132 | ImageWidgetAssoc: 42 | Image: 42 | Page: 2 | User: 1 | Role: 1 | Account: 1 | PageVariant: 1
Oink Log Entry Complete

I am moving to raw-sql, bye bye!

[Ch4s3]

Any fixes yet?

[seamusabshere]

hi,

unfortunately upsert does not currently support json or jsonb. at the time it was written, hstore was the only game in town (or, at least, the only one i knew about).

[Ch4s3]

Are you open to a PR for json?

[seamusabshere]

@Ch4s3 definitely!

[Ch4s3]

I'll take a look with my team next week

[fc-arny]

@Ch4s3

unfortunately upsert does not currently support json or jsonb. at the time it was written, hstore was the only game in town (or, at least, the only one i knew about).

Temporary solution:

Mode.upsert(name: 'John', settings: {ip: '192.168.0.1'}.to_json)

I hope this helps.

[Ch4s3]

Yeah, I'm doing something like that.

[pnomolos]

@Ch4s3 Have you been working on a PR for json? If not I'll take a look at this one because I'm sure we'll be using it in the near future :)

[seamusabshere]

(we should do json and jsonb obv)

[pnomolos]

@seamusabshere json will need some extra work if it's being used in the selector fields, as there's no native comparison operator for it.

[seamusabshere]

(you mean selector fields?) we should probably just disallow it in the selectors.

[pnomolos]

@seamusabshere Yes, that's what I meant :) That'd probably be the best.

[Ch4s3]

@pnomolos I never got around to this unfortunately. I've been using to_json to upsert things and it seems to work well enough.