Repository navigation
docs(spec): state the JSON form of every type each database writes - #159
Conversation
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
There was a problem hiding this comment.
🟡 Changes recommended
The type-descriptor documentation misclassifies several numeric types as JSON strings and incorrectly describes hexadecimal as the only bytea encoding.
2 open issues
What changed in this PR
This documentation-only PR expands the type descriptor specification’s JSON representation rules, clarifying how database-generated JSON relates to SQL leaf types.
Changes:
- Documents Postgres JSON forms for additional types and a text-string fallback.
- Adds MySQL rules for temporal, enum, set, bit and spatial values.
- Leaves executable code and type definitions unchanged.
| File | Description |
|---|---|
spec/type-descriptor.ts |
Expands comments describing Postgres and MySQL JSON representations. |
Review effort: Balanced
| // - Postgres: | ||
| // - a `numeric` is a JSON number of any length, and a NaN or Infinity a string; | ||
| // - a `boolean` is `true` or `false`; | ||
| // - a `numeric` is a JSON number of any length, and a float's or a `numeric`'s NaN or Infinity a string; |
| // - an array is a JSON array of its elements, and a composite or any other row an object of its fields (`row(1, 'x')` is `{"f1":1,"f2":"x"}`); | ||
| // - an `hstore` is an object of nullable text, and PostGIS's `geometry` GeoJSON; | ||
| // - `jsonb` sorts keys and keeps the last duplicate, while `json` keeps the text as written; | ||
| // - every other type is a string of its text, structured or not: `bit` and `varbit` (`"101"`), the geometric types (`"(1,2)"`, `"[(0,0),(1,1)]"`), `inet`, `cidr`, `macaddr` and `macaddr8`, ranges and multiranges (`"[1,5)"`), `interval` in the session's IntervalStyle (`"1 mon -1 days"`), `time`, `uuid`, `money`, `oid`, `xml`, and `bytea` in hex (`"\\x0102"`). |
There was a problem hiding this comment.
ℹ️ Two gaps the new catch-all opens. Details inline.
Reviewed changes
Reviewed the update to the spec's facts on what Postgres and MySQL write inside their own JSON.
- Postgres general rule: adds booleans,
date/timestamp, arrays, rows, PostGISgeometry, and a catch-all saying every other type is a string of its text. - MySQL additions:
TIME,YEAR,ENUM,SET,BITand spatial values.
The MySQL claims match Json_wrapper's output. On Postgres, json_categorize_type agrees with the structured cases and with oid as a string. Two things are off because the new catch-all now applies to every type the list doesn't name.
claude-opus-5-5 ｜ 𝕏
There was a problem hiding this comment.
✅ No new issues found. All three earlier comments are fixed.
Reviewed changes
Reviewed 67fdd74, which revises the Postgres list of JSON forms in response to the earlier review.
- Postgres numbers: now says integers, floats and
numericare all JSON numbers. A float's or anumeric's NaN or Infinity is a string. This matchesjson_categorize_type'sJSONTYPE_NUMERICgroup and the check that quotes textIsValidJsonNumberrejects. - Postgres dates: now gives the BC suffix and
"infinity"/"-infinity"for all three ofdate,timestampandtimestamptz. This matchesEncodeDateOnly/EncodeDateTimeunderUSE_XSD_DATESandEncodeSpecialDate/EncodeSpecialTimestamp. bytea: now written in the session'sbytea_output, hex by default, the same wayintervalfollows IntervalStyle.
claude-opus-5-5 ｜ 𝕏
The spec types each leaf of JSON
contentby the SQL value the database wrote it from, and its facts listed only five forms per database. Most of the types the spec structures are written as plain strings inside JSON, so a consumer reading a leaf by its type alone would expect a JSON array or object where the database wrote text.Measured on Postgres 18 and MySQL 8.4:
The facts now state each database's general rule: on Postgres, booleans, numbers, dates and timestamps, arrays, rows,
hstoreand PostGISgeometryhave JSON forms of their own and every other type is a string of its text; on MySQL,TIME,YEAR,ENUM,SET,BITand spatial values are added.