2

When I do:

SELECT cast('{"viewport" : {               "northeast" : {                  "lat" : 40.7155809802915,                  "lng" : -73.9599399197085               },              "southwest" : {                  "lat" : 40.7128830197085,                  "lng" : -73.96263788029151               }            }}' AS json);

I get this:

"{"viewport" : {               "northeast" : {                  "lat" : 40.7155809802915,                  "lng" : -73.9599399197085               },              "southwest" : {                  "lat" : 40.7128830197085,                  "lng" : -73.96263788029151               }            }}"

I want want to convert it to something with no whitespaces or fewer whitespaces. Like this:

"{"viewport" : {"northeast" : {"lat" : 40.7155809802915, "lng" : -73.9599399197085 }, "southwest" : { "lat" : 40.7128830197085, "lng" : -73.96263788029151 }}}"
Dojo
  • 5,374
  • 4
  • 49
  • 79
  • JSON is JSON (non-data whitespace ignored). Printing is printing. Why mix the two? OCD? – David-SkyMesh May 31 '14 at 01:28
  • @David-SkyMesh To save disk space – Dojo May 31 '14 at 07:43
  • Then why use JSON at all? It's not a particularly space-efficient representation. – David-SkyMesh May 31 '14 at 08:24
  • I am not here to educate you on the advantages of using JSON in database. There's a reason why Postgres has JSON support a priority. – Dojo May 31 '14 at 08:39
  • Pray, don't let any of your 'education' slip your grasp! I'm not the first and won't be the last to 'fail to absorb the groupthink' on using a document/unstructured storage paradigm for (vast majority of the time) innappropriate reasons. – David-SkyMesh May 31 '14 at 10:00
  • Per my comment, however, others have already commented that Postgres is moving to `jsonb` as a storage back-end for `json` type, which *is* designed to be space-efficient. I merely asked why you'd choose to store JSON in the way that's currently supported if your overarching goal was space efficiency. Without context, you sounded (sound) like the perpetrator of an XY. – David-SkyMesh May 31 '14 at 10:00
  • @David-SkyMesh I already know JSON is not the most space efficient format. I want to use it for its benefits and some overhead is tolerable for this but I don't want to overpay by storing whitespace. If you want to buy an expensive car, its not like you stop caring about the price. You want the car and still want a good deal. I am also aware that JSONB is much more efficient (and I will very likely use it when 9.4 becomes GA). I posted this question because I thought this whitespace trimming thing is pretty basic even for an early release and I was expecting some native support for it. – Dojo May 31 '14 at 10:19
  • @David-SkyMesh I don't know what you mean by "perpetrator of an XY" but you sure sound like you want to prove you are smarter by telling me that JSON is not the most efficient format. I already know its not, every body know that, it's common knowledge. To preempt the next question: "If you know JSON is not efficient and still chose it, why cry over extra witespace?" My answer: Please refer to the expensive car analogy above. – Dojo May 31 '14 at 10:26
  • XY problem is that OP asks for help with X when they could give some more background that would make it apparent that they really should be asking Y -- in the the process getting better advice. – David-SkyMesh May 31 '14 at 13:00

2 Answers2

4

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.

lpsmith
  • 707
  • 3
  • 8
  • I know it isn't too difficult to implement a custom fix, was just trying to find whether there is a native solution. I guess, I'll wait for 9.4 which seems to be a major improvement on the JSON front. – Dojo May 31 '14 at 07:49
3
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.95

99399197085}}}

-g

Community
  • 1
  • 1
Greg
  • 6,571
  • 2
  • 27
  • 39