4

I'm looking to create a hashing function for jsonb fields, but need to control the casting to string to allow for client and server integrity. In other words, I'd like to stringify JSON on both client and server with the same spacing and indentation.

For example, this query:

SELECT '{"tags":["tag1","tag2"],"c":1}' ::JSONB ::TEXT

will return:

{"c": 1, "tags": ["tag1", "tag2"]}

Now, other than ordering keys (which is ideal), here is the interesting part. It puts spaces after colons (:), compared to JSON.stringify on client:

{"tags":["tag1","tag2"],"c":1}

Notice there are no spaces after colons. Ordering of keys can be done using something like json-stable-stringify.

Ideally we can control the output on server... I found in the source code a function that looks like it's used for casting JSONB to string: a function JsonbToCString which returns a JsonbToCStringWorker, and in seems like there are arguments to controlling spacing and indentation https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/jsonb.c#L430

Is there anyway to expose these methods similar to how you can call functions in psql? Something like SELECT json_to_string( ..., indent ). I'm happy to use the ::TEXT casting method, but seems like the stringification is a bit hard-coded and I cannot pass indent options.

Thanks in advance for your help!

pyramation
  • 1,631
  • 4
  • 22
  • 35
  • Are you looking for `jsonb_pretty()`? –  Jun 07 '17 at 20:41
  • from what I can tell, `jsonb_pretty(obj)` = `JSON.stringify(obj, null, 4)`, so if this is consistent, I would say, yes! – pyramation Jun 07 '17 at 20:59
  • I'd definitely avoid depending on particular order and text representation of jsonb type. You should use json type to store output of `json-stable-stringify` instead. Your code will likely break on database upgrade or even maybe during a backup restore. – Tometzky Jun 07 '17 at 21:15
  • yea I'm leaning towards this. The ideal scenario would be the database could also compute hashes using a reliable serialization model, but maybe keeping the two in sync would cause issues as you mention. – pyramation Jun 07 '17 at 21:18
  • yep, the more I think about it @Tometzky I think your idea sounds most reliable since json will preserve whitespace and key order. – pyramation Jun 07 '17 at 21:21

0 Answers0