How to trim whitespaces in JSON datatype of Postgres?
I'm not specifically aware of any json printer inside postgresql itself at the moment. You could implement a proper server-side function, which shouldn't be too difficult. But it's probably best at the moment to parse and pretty print json inside your application before you insert it into the database.
It's important to remember that the json type is just a string, that conceptually has a check constraint that ensures it's actually valid json syntax. That's all. Consequently, json manipulation inside of postgresql itself can be rather inefficient.
With the jsonb type coming in postgresql 9.4 will fix your problem, as it's not storing the json itself, but rather an abstract representation that's more amenable to efficient manipulation. Thus storing a jsonb value will forget about any extraneous white space, and you'll probably get back a minimal json string generated from a printer.
SELECT cast(replace('{"viewport" : { "northeast" : { "lat" : 40.7155809802915, "lng" : -73.9599399197085 }, "southwest" : { "lat" : 40.7128830197085, "lng" : -73.96263788029151 } }}', ' ', '') AS json);yields
{"viewport":{"northeast":{"lat":40.7155809802915,"lng":-73.9599399197085},"southwest":{"lat":40.7128830197085,"lng":-73.96263788029151}}}However, I would have thought a cast would accomplish this! Also, this will fail if there is a space as part of any key name.
Updated answer for issue with potential spaces in key or value:
SELECT cast(regexp_replace('{"viewport here" : { "northeast there" : { "lat" : 40.7155809802915, "lng" : -73.9599399197085 }, "southwest" : { "iggy and": "squiggy or something", "lat" : 40.7128830197085, "lng" : -73.96263788029151 } }}', '( )(?=(?:[^"]|"[^"]*")*$)', '', 'g') AS json);returns
{"viewport here":{"northeast there":{"lat":40.7155809802915,"lng":-73.9599399197085},"southwest":{"iggy and":"squiggy or something","lat":40.7128830197085,"lng":-73.96263788029151}}}
here is the postgres documentation:http://www.postgresql.org/docs/9.3/static/functions-matching.html
here is the stack overflow i found on this specific regex:Regex to pick commas outside of quotes
@lpsmith: indeed, in 9.4 this works :
SELECT cast('{"viewport here" : { "northeast there" : { "lat" : 40.7155809802915, "lng" : -73.9599399197085 }, "southwest" : { "iggy and": "squiggy or something", "lat" : 40.7128830197085, "lng" : -73.96263788029151 } }}' AS jsonb);producing this:
{"viewport here": {"southwest": {"lat": 40.7128830197085, "lng": -73.96263788029151, "iggy and": "squiggy or something"}, "northeast there": {"lat": 40.7155809802915, "lng": -73.9599399197085}}}
-g