json type does not preserve the document text it was given
- 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
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