Converting to postgres jsonb

[nathanprayzo]

I attempted to migrate our postgres db over to jsonb by manually running the following commands on our heroku database:

-- We have to remove the default value of '{}'::json because it's incompatible with jsonb
ALTER TABLE reading ALTER COLUMN readings DROP DEFAULT;
-- Change the column type, and cast existing data to jsonb
ALTER TABLE reading ALTER COLUMN readings TYPE jsonb USING readings::jsonb;
-- Add the default value back, of course using jsonb this time
ALTER TABLE reading ALTER COLUMN readings SET DEFAULT '{}'::jsonb;
-- Modify json_object_set_key function
CREATE OR REPLACE FUNCTION "json_object_set_key"(
  "jsonb"         jsonb,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
     FROM (SELECT *
             FROM json_each("jsonb")
            WHERE "key" <> "key_to_set"
            UNION ALL
           SELECT "key_to_set", to_json("value_to_set")) AS "fields"),
  '{}'
)::jsonb
$function$;

As well as modifying our PostgresManager AddReading() function to cast the new readings to jsonb result, err := m.db.Exec( UPDATE reading SET readings = json_object_set_key(readings, $1, $2::jsonb) WHERE device_id = ( SELECT id FROM device WHERE identifier = $3 ), r.CreatedAt, b, r.Device.Identifier)

However the application is still throwing the following error: 2014-12-31T16:55:17.024619+00:00 app[web.1]: 2014/12/31 16:55:17 http: panic serving 10.101.131.90:24434: pq: column "readings" is of type jsonb but expression is of type json 2014-12-31T16:55:17.024627+00:00 app[web.1]: goroutine 1950 [running]: 2014-12-31T16:55:17.024628+00:00 app[web.1]: net/http.func��011() 2014-12-31T16:55:17.024630+00:00 app[web.1]: /usr/local/go/src/pkg/net/http/server.go:1100 +0xb7 2014-12-31T16:55:17.024632+00:00 app[web.1]: runtime.panic(0x8592c0, 0xc208002ea0) 2014-12-31T16:55:17.024634+00:00 app[web.1]: /usr/local/go/src/pkg/runtime/panic.c:248 +0x18d 2014-12-31T16:55:17.024635+00:00 app[web.1]: main.func��002(0x7feab7b1a6e0, 0xc2080525a0, 0xc208029380) 2014-12-31T16:55:17.024637+00:00 app[web.1]: /tmp/build_9e354841822788b7992fed60c0fbeb15/crakalakin-aquaponics-data-9bece2f3f4480206e082588281cc30173759a96d/.heroku/g/src/github.com/crakalakin/aquaponics-data/main.go:101 +0x43f 2014-12-31T16:55:17.024640+00:00 app[web.1]: net/http.HandlerFunc.ServeHTTP(0xc208001a90, 0x7feab7b1a6e0, 0xc2080525a0, 0xc208029380) 2014-12-31T16:55:17.024642+00:00 app[web.1]: /usr/local/go/src/pkg/net/http/server.go:1235 +0x40 2014-12-31T16:55:17.024645+00:00 app[web.1]: github.com/gorilla/mux.(_Router).ServeHTTP(0xc2080199a0, 0x7feab7b1a6e0, 0xc2080525a0, 0xc208029380) 2014-12-31T16:55:17.024648+00:00 app[web.1]: /tmp/build_9e354841822788b7992fed60c0fbeb15/crakalakin-aquaponics-data-9bece2f3f4480206e082588281cc30173759a96d/.heroku/g/src/github.com/crakalakin/aquaponics-data/Godeps/_workspace/src/github.com/gorilla/mux/mux.go:98 +0x292 2014-12-31T16:55:17.024650+00:00 app[web.1]: net/http.(_ServeMux).ServeHTTP(0xc2080226c0, 0x7feab7b1a6e0, 0xc2080525a0, 0xc208029380) 2014-12-31T16:55:17.024652+00:00 app[web.1]: /usr/local/go/src/pkg/net/http/server.go:1511 +0x1a3 2014-12-31T16:55:17.024655+00:00 app[web.1]: net/http.serverHandler.ServeHTTP(0xc208004f60, 0x7feab7b1a6e0, 0xc2080525a0, 0xc208029380) 2014-12-31T16:55:17.024657+00:00 app[web.1]: /usr/local/go/src/pkg/net/http/server.go:1673 +0x19f 2014-12-31T16:55:17.024659+00:00 app[web.1]: net/http.(_conn).serve(0xc208050780) 2014-12-31T16:55:17.024661+00:00 app[web.1]: /usr/local/go/src/pkg/net/http/server.go:1174 +0xa7e 2014-12-31T16:55:17.024663+00:00 app[web.1]: created by net/http.(_Server).Serve 2014-12-31T16:55:17.024665+00:00 app[web.1]: /usr/local/go/src/pkg/net/http/server.go:1721 +0x313

[brianfoshee]

@letsgitgrowing just after a quick glance, should this line in the sql function: SELECT "key_to_set", to_json("value_to_set")) AS "fields"), be: SELECT "key_to_set", to_jsonb("value_to_set")) AS "fields"),?

[nathanprayzo]

@crakalakin to_jsonb doesn't exist but I did find the problem, which was that I forgot to specify the "RETURNS" field to jsonb and instead of SELECT "key_to_set", to_json("value_to_set")) AS "fields"), it needed to be: SELECT "key_to_set", "value_to_set") AS "fields"),

Thus the create or replace function for jsonb is:

CREATE OR REPLACE FUNCTION "json_object_set_key"(
  "jsonb"          jsonb,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS jsonb
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
     FROM (SELECT *
             FROM jsonb_each("jsonb")
            WHERE "key" <> "key_to_set"
            UNION ALL
           SELECT "key_to_set", "value_to_set") AS "fields"),
  '{}'
)::jsonb
$function$;

[brianfoshee]

@letsgitgrowing gotcha. This is an interesting article on understanding some of the jsonb functions in posetgres. I added it to the wiki. It has an alternative function for merging json into a json field.

http://michael.otacoo.com/postgresql-2/manipulating-jsonb-data-with-key-unique/

[nathanprayzo]

@crakalakin Great article, thanks for the post. I have gone ahead and wiped the database clean and am now benchmarking jsonb; I'll post results within the hour.

[nathanprayzo]

JSON:

JSONB:

I suspect the total throughput is dependent on the # of requests my computer can generate, as well as general ISP network latency. Throughput aside, it appears using JSONB may improve response time; i'll let it run overnight and grab another screenshot in the morning.