Postgres Jsonb Column how to maintain json object order of parameters

Question

I Have a use case, where I generate a hash of a JSON object created in java initially and insert the hash and JSON object into the Postgres table as String and jsonb respectively.

I need to validate the JSON object saved initially in regular intervals, During that I fetch the JSON object from Postgres which is stored in jsonb and generate a hash out of it and compare it with the hash generated initially. Both are different now.

The reason is initially when data was inserted order of parameters was different in JSON Object at retrieval the order is different. Ending up generating 2 different hash for the same data.

Please suggest.

The best solution is to use the JSONB datatype and generate the hash inside of PostgreSQL. — Mike Organek
– Mike Organek, Commented Sep 22, 2022 at 14:35

Adrian Klaver · Accepted Answer · 2022-09-22 16:00:06Z

4

Use json type instead of jsonb per JSON:

Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

answered Sep 22, 2022 at 16:00

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Jsonb Column how to maintain json object order of parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related