Thread
-
BUG #19110: the order of elements in a json object in database is different then the order of elements
PG Bug reporting form <noreply@postgresql.org> — 2025-11-11T14:26:34Z
The following bug has been logged on the website: Bug reference: 19110 Logged by: Tal Tabakman Email address: tal.tabakman@foretellix.com PostgreSQL version: 14.0 Operating system: ubuntu 22.04 Description: Hi, i am using a Kotlin based application which used spring-boot3.5.6 + hibernate6 on top of postgresql14 i have LinkedHashMap construct defined as follows @JdbcTypeCode(SqlTypes.JSON) @Column(name = "compound_rules", columnDefinition = "json") var compoundRules: LinkedHashMap<String, CompoundRuleData>, This hash map is stored as a json object under a column defined as a json column (and not jsonb) what we are noticing is that when we send the relevant object from our application to postgres and fetch it back (using update and select queries respectivally) , the order of elemenet in the hash map is changing which causes a problem in our end. sevaral facts 1. what we see is that the linked hash map is serialized to a json string in a way that preserve the order of elements , this is a key feature of linked hash maps which we relay on 2. we also see that the correct json string is properly sent via socket to postgres. 3. However, the log in postgres side shows a changed order of elements This is a log file from the application side, prior to sending the data to posgres (note the order of "test_rule_0" and "test_rule_1") 2025-11-09 12:21:47,140 [TRACE] [http-nio-8080-exec-4] [opId=ae543957aff2] [userEmail=admin@fmanager.com] [ResourceRegistryStandardImpl.java:96] Releasing statement [/* update for com.foretellix.manager.services.workspace.WorkspaceCompoundRulesData */update workspace_compound_rules set compound_rules=(‘{“4d3f85b4962c50fd7f38”:{“id”:“4d3f85b4962c50fd7f38”,“name”:“test_rule_0”,“modifiedAt”:1762683706,“lastModifiedById”:“42e6b2f773365a5fae82”,“createdById”:“42e6b2f773365a5fae82”,“rulesParams”:{“temporalRelationParams”:{“intervalFilters”:[{“elements”:[{“_type”:“intervals”,“include”:true,“scenarioName”:null,“protected”:false,“logicalOperator”:“AND”,“intervalName”:“sut.lead_vehicle”,“intervalType”:“MatchIntervalData”,“childrenFilter”:null,“intervalCriteria”:}],“logicalOperator”:“AND”,“isEmpty”:false},{“elements”:[{“_type”:“intervals”,“include”:true,“scenarioName”:null,“protected”:false,“logicalOperator”:“AND”,“intervalName”:“sut.follower_vehicle”,“intervalType”:“MatchIntervalData”,“childrenFilter”:null,“intervalCriteria”:}],“logicalOperator”:“AND”,“isEmpty”:false}],“timeRelation”:“ANY_INTERSECTION”,“customTimeRelation”:null,“workspaceId”:“4a4cafc9c441ec77fc08”},“temporalAction”:“UNION”,“nameParams”:{“name”:“test_interval”,“concatNames”:false,“delimiter”:“_”},“metricGroupPrefix”:null,“corrMetricGroupPrefix”:null},“creationContext”:“WORKSPACE”,“createdAt”:1762683706},“414981a5b9d77c76829a”:{“id”:“414981a5b9d77c76829a”,“name”:“test_rule_1”,“modifiedAt”:1762683707,“lastModifiedById”:“42e6b2f773365a5fae82”,“createdById”:“42e6b2f773365a5fae82”,“rulesParams”:{“temporalRelationParams”:{“intervalFilters”:[{“elements”:[{“_type”:“intervals”,“include”:true,“scenarioName”:null,“protected”:false,“logicalOperator”:“AND”,“intervalName”:“sut.lead_vehicle”,“intervalType”:“MatchIntervalData”,“childrenFilter”:null,“intervalCriteria”:}],“logicalOperator”:“AND”,“isEmpty”:false},{“elements”:[{“_type”:“intervals”,“include”:true,“scenarioName”:null,“protected”:false,“logicalOperator”:“AND”,“intervalName”:“sut.follower_vehicle”,“intervalType”:“MatchIntervalData”,“childrenFilter”:null,“intervalCriteria”:}],“logicalOperator”:“AND”,“isEmpty”:false}],“timeRelation”:“ANY_INTERSECTION”,“customTimeRelation”:null,“workspaceId”:“4a4cafc9c441ec77fc08”},“temporalAction”:“UNION”,“nameParams”:{“name”:“test_interval”,“concatNames”:false,“delimiter”:“_”},“metricGroupPrefix”:null,“corrMetricGroupPrefix”:null},“creationContext”:“WORKSPACE”,“createdAt”:1762683707}}’),version=(‘2’::int4),workspace_id=(‘4a4cafc9c441ec77fc08’) where id=(‘4643b0cbd1fce539cc70’)] however, if looking at the postgresql log message exactly at that timestamp we will see that the order of elements is reversed (note the order of "test_rule_1" and "test_rule_0") 2025-11-09 12:21:47.140 IST [3182906] fmanager@fmanager LOG: execute : /* update for com.foretellix.manager.services.workspace.WorkspaceCompoundRulesData */update workspace_compound_rules set compound_rules=$1,version=$2,workspace_id=$3 where id=$4 2025-11-09 12:21:47.140 IST [3182906] fmanager@fmanager DETAIL: parameters: $1 = ‘{“414981a5b9d77c76829a”: {“id”: “414981a5b9d77c76829a”, “name”: “test_rule_1”, “createdAt”: 1762683707, “modifiedAt”: 1762683707, “createdById”: “42e6b2f773365a5fae82”, “rulesParams”: {“nameParams”: {“name”: “test_interval”, “delimiter”: “_”, “concatNames”: false}, “temporalAction”: “UNION”, “metricGroupPrefix”: null, “corrMetricGroupPrefix”: null, “temporalRelationParams”: {“workspaceId”: “4a4cafc9c441ec77fc08”, “timeRelation”: “ANY_INTERSECTION”, “intervalFilters”: [{“isEmpty”: false, “elements”: [{“_type”: “intervals”, “include”: true, “protected”: false, “intervalName”: “sut.lead_vehicle”, “intervalType”: “MatchIntervalData”, “scenarioName”: null, “childrenFilter”: null, “logicalOperator”: “AND”, “intervalCriteria”: }], “logicalOperator”: “AND”}, {“isEmpty”: false, “elements”: [{“_type”: “intervals”, “include”: true, “protected”: false, “intervalName”: “sut.follower_vehicle”, “intervalType”: “MatchIntervalData”, “scenarioName”: null, “childrenFilter”: null, “logicalOperator”: “AND”, “intervalCriteria”: }], “logicalOperator”: “AND”}], “customTimeRelation”: null}}, “creationContext”: “WORKSPACE”, “lastModifiedById”: “42e6b2f773365a5fae82”}, “4d3f85b4962c50fd7f38”: {“id”: “4d3f85b4962c50fd7f38”, “name”: “test_rule_0”, “createdAt”: 1762683706, “modifiedAt”: 1762683706, “createdById”: “42e6b2f773365a5fae82”, “rulesParams”: {“nameParams”: {“name”: “test_interval”, “delimiter”: “_”, “concatNames”: false}, “temporalAction”: “UNION”, “metricGroupPrefix”: null, “corrMetricGroupPrefix”: null, “temporalRelationParams”: {“workspaceId”: “4a4cafc9c441ec77fc08”, “timeRelation”: “ANY_INTERSECTION”, “intervalFilters”: [{“isEmpty”: false, “elements”: [{“_type”: “intervals”, “include”: true, “protected”: false, “intervalName”: “sut.lead_vehicle”, “intervalType”: “MatchIntervalData”, “scenarioName”: null, “childrenFilter”: null, “logicalOperator”: “AND”, “intervalCriteria”: }], “logicalOperator”: “AND”}, {“isEmpty”: false, “elements”: [{“_type”: “intervals”, “include”: true, “protected”: false, “intervalName”: “sut.follower_vehicle”, “intervalType”: “MatchIntervalData”, “scenarioName”: null, “childrenFilter”: null, “logicalOperator”: “AND”, “intervalCriteria”: }], “logicalOperator”: “AND”}], “customTimeRelation”: null}}, “creationContext”: “WORKSPACE”, “lastModifiedById”: “42e6b2f773365a5fae82”}}’, $2 = ‘2’, $3 = ‘4a4cafc9c441ec77fc08’, $4 = ‘4643b0cbd1fce539cc70’ can you shed some light on what causing this ? it seems that in the postgres side there is a ordering of elements based on the "id" lexical value thanks Tal -
Re: BUG #19110: the order of elements in a json object in database is different then the order of elements
Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-11T15:16:03Z
PG Bug reporting form <noreply@postgresql.org> writes: > This hash map is stored as a json object under a column defined as a json > column (and not jsonb) > what we are noticing is that when we send the relevant object from our > application to postgres and fetch it back (using update and select queries > respectivally) , the order of elemenet in the hash map is changing which > causes a problem in our end. The only way this could possibly be happening on the Postgres server side is if the value is being cast to jsonb on its way to the table. I'd check issues like how the prepared statement's parameter is declared. If you are going through JDBC or another client-side stack, it's possible that that is substituting jsonb for json in the command issued to the server. You might need to pretend that the parameter is plain text to avoid that. regards, tom lane