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