dolthub / dolthub/doltgresql

json type does not preserve the document text it was given

Open
#3,208 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 10h
Merged PRs (30d)
129

Description

Postgres treats `json` as validated text: the document is stored exactly as it was given, and only `jsonb` normalizes it. Doltgres parses both types into an in-memory document on input, so a `json` value loses the properties Postgres guarantees for it — whitespace, key order, duplicate keys, and the exact form of escapes and numbers. The rendering also depends on whether the value came from storage or not, so the same input can print two different ways.

```sql
SELECT '{"b":1, "a":2}'::json;
-- postgres: {"b":1, "a":2}
-- doltgres: {"a": 2, "b": 1}

SELECT '{"a":1,"a":2}'::json;
-- postgres: {"a":1,"a":2}
-- doltgres: {"a": 2}

SELECT '{"a":"\u003c"}'::json;
-- postgres: {"a":"\u003c"}
-- doltgres: {"a": "<"}

SELECT '{"a":1.000,"b":1e2}'::json;
-- postgres: {"a":1.000,"b":1e2}
-- doltgres: {"a": 1, "b": 100}
```

The same value prints differently once it has been through storage, because a stored `json` value and an in-flight one are rendered by different paths:

```sql
CREATE TABLE t (id int PRIMARY KEY, j json);
INSERT INTO t VALUES (1, '{"b":1, "a":2}');

SELECT j FROM t; -- doltgres: {"a":2,"b":1} (compact, keys sorted lexically)
SELECT j::text FROM t; -- doltgres: {"a":2,"b":1}
SELECT '{"b":1, "a":2}'::json; -- doltgres: {"a": 2, "b": 1} (spaced, keys sorted by length)
-- postgres returns the input text verbatim in all three
```

Numbers are the part that is not merely cosmetic, and it affects **`jsonb` as well**: numeric literals are round-tripped through a float, so a value can come back changed. Postgres keeps the token text for `json` and stores `jsonb` numbers as `numeric`, so both preserve the value.

```sql
SELECT '{"n":12345678901234567890,"d":0.1234567890123456789}'::json;
-- postgres: {"n":12345678901234567890,"d":0.1234567890123456789}
-- doltgres: {"d": 0.12345678901234568, "n": 12345678901234567000}

SELECT '{"n":12345678901234567890,"d":0.1234567890123456789}'::jsonb;
-- postgres: {"d": 0.1234567890123456789, "n": 12345678901234567890}
-- doltgres: {"d": 0.12345678901234568, "n": 12345678901234567000}
```

Functions that inspect a `json` document inherit the gap and cannot be made compliant on their own, since the information is already gone by the time they see the value:

```sql
SELECT * FROM json_object_keys('{"z":0,"a":1,"a":2,"bb":3}');
-- postgres: z, a, a, bb (document order, duplicate reported twice)
-- doltgres: a, z, bb

SELECT json_strip_nulls('{"b":1,"a":null,"a":2}');
-- postgres: {"b":1,"a":2}
-- doltgres: {"a": 2, "b": 1}
```

Worth noting for whoever picks this up: making `json` carry its text is a change to what gets persisted for a given input, so it needs to be weighed as a storage-semantics change rather than a function-level fix, and existing rows would keep whatever text was written for them.

Contributor guide

Open the contributing guide

Research direction

Start by tracing the json and jsonb input, storage, and rendering paths, then compare their behavior with PostgreSQL's preservation semantics. Use the SQL examples in the issue as regression cases, including whitespace, duplicate keys, escapes, numeric precision, and stored versus in-flight values. Done means json preserves its input text and jsonb preserves numeric values without losing the documented information.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
databases
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Active
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.