3

The answer to my question here: Detecting column changes in a postgres update trigger has me converting rows in my database into their hstore equivalent. It's clever, but leads to serialization / deserialization issues with array column types.

I have a few array-typed columns, so for example: 

select hstore_to_json(hstore(documents.*)) from documents where id=283;

gives me (abbreviated form): 

 {"id": "283", "tags": "{potato,rutabaga}", "reply_parents": "{7}"}

What I'd really like is

"tags": ["potato", "rutabaga"], "reply_parents": [7]

as this is well-formed JSON. Technically, the first response is also well-formed JSON, as the array has been stringified and is sent down the wire as "{potato,rutabaga}". This requires me to fiddle with the parsing of responses I get, and while that's not the end of the world, it is a bit of a pain if it turns out to be unnecessary.

Calling row_to_json on the row first converts the array types into their proper json-array representation, but it doesn't seem like there's a set subtraction type operator on json objects (hstore - hstore in the question asked above is how I'm sending "these columns changed" events down my websocket wire). So, any suggestions as to how to get this working properly right in the database are welcome. (either futzing with the way arrays get stringified into hstore, or by doing set subtraction on json objects).

Community
  • 1
  • 1
pfooti
  • 2,605
  • 2
  • 24
  • 37
  • Why are you converting a row to hstore and then converting that hstore to JSON? Wouldn't it make more sense to go straight to JSON? – mu is too short May 22 '14 at 20:15
  • @muistooshort see the linked question. I'm doing this in an on update trigger, to determine which columns have changed. So I can do `hstore(NEW.*) - hstore(OLD.*)` in the trigger and get the changed fields. Sadly the json datatype doesn't have the same kind of operation. – pfooti May 22 '14 at 21:49
  • The `"id": "283"` is also a problem, that really should be `"id":283`. The underlying issue is that everything gets stringified because you're using hstore. I don't think hstore and json are (currently) appropriate for what you're trying to do. Maybe you should do it all by hand until the improved jsonb stuff is available. – mu is too short May 22 '14 at 22:55
  • True enough, although I'm already dealing with string/int issues in general, and parseInt is called behind the scenes when accessing objects by string indexes in javascript (e.g., foo['1'] === foo[1]). Also: hstore_to_json_loose will dequote integers and booleans and the like, so it seems preferable to the other. – pfooti May 22 '14 at 23:18

1 Answers1

2

If you cannot find any natural solution, you can always trust in regex.

create or replace function hj(json text)
returns text language plpgsql immutable
as $$
begin
    return 
        regexp_replace(
            regexp_replace(
                regexp_replace(
                    json, '([^"{]+?),', '"\1", ', 'g'),
                '([^"{ ]+?)}"', '"\1"]', 'g'),
            '"{"', '["', 'g');
end $$;

select hj('{"id": "283", "tags": "{potato,rutabaga}", "reply_parents": "{7}"}');
-- gives:
-- {"id": "283", "tags": ["potato", "rutabaga"], "reply_parents": ["7"]}
klin
  • 112,967
  • 15
  • 204
  • 232
  • This is the plpgsql version of what I'm currently doing - on the other end of the wire is my javascript front end, that says, "hey, if JSON.parse gave me a string instead of an expected array, I'm going to turn it into an array via regexes". It works okay (especially since the js ORM I've written expects data on the wire to be typed - I really oughta find a way to extract that from the db automagically one of these days). – pfooti May 22 '14 at 23:21
  • I don't catch this subtle difference. The server just gives you what you want. I'm not going to argue with you, of course :) – klin May 22 '14 at 23:46