hasura / hasura/graphql-engine

JSON key ordering is lost when inserting JSON values

Open
#8,453 5 comments 1 reaction 0 assignees View on GitHub
k/bug
Dominant language
TypeScript
Stars
32.1k
Forks
3k
PR merge metrics
PR metrics pending

Description

### Version Information

Server Version: 2.5.1

### Environment

Cloud / OSS

### What is the expected behaviour?

Inserting JSON values into JSON or JSONB columns should insert the data as-is, with no changes to the data being inserted.

### Keywords

JSON JSONB parsing

### What is the current behaviour?

Inserting JSON values into JSON or JSONB columns on postgres will re-order the object keys in unpredictable ways.
There is no clear pattern to the changes, and with some payloads JSON and JSONB columns are re-ordered differently.

### How to reproduce the issue?
Due to the unpredictable nature of the issue, I am unsure if this will replicate consistently on every machine.
If the below does not work, I suggest you try again with increasingly complex JSON payloads.

1. Create and track the following table:
```sql
CREATE TABLE json_test (
id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
data JSON,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```
2. Execute the following mutation:
```graphql
mutation InsertJsonTest ($data: json!) {
insert_json_test_one (object: {
data: $data
}) {
data
}
}
```
variables:
```json
{
"data": { "b": "value", "a": "value" }
}
```
3. The mutation returns the following payload:
```json
{
"data": {
"insert_json_test_one": {
"data": {
"a": "value",
"b": "value"
}
}
}
}
```
You can see the key order has changed. This is also apparent if you check the database directly, the re-ordering happens during the insert.

My best guess is that because JSON payloads are passed as parts of the variables object, they get parsed with the object all the way down, then stringified again, and something goes wrong during this process.

### Any possible solutions?

Currently there are no issues with queries, only mutations.
If ordering of keys is critical, users would need to resort to performing inserts themselves using a third party postgres client.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the issue with the provided json_test table, GraphQL mutation, and variables, then compare the inserted and returned values for JSON and JSONB columns. Trace how mutation variables are serialized before PostgreSQL insertion; done means the supported JSON insertion path preserves the expected key ordering, with regression coverage for the reproduction.

Written by the indexing model from the issue text.

Assessment

Tech stack
graphql, postgresql
Domain
api, backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.