Kysely•4w ago

Inconsistent Typing of NUMERIC Fields with jsonBuildObject

https://kyse.link/wxJiK I came across an issue on GitHub (link) where JSON fields in PostgreSQL aren't automatically typed correctly due to everything being returned as a string. This means that when using jsonObjectFrom, the returned type is string, rather than the expected data type like Date. What's puzzling is that this behavior isn't consistent across all data types. For instance, NUMERIC values at the root level are returned as strings (which is fine), but when those same values appear inside a nested JSON object, their type is inferred as number instead. I would prefer to have NUMERIC values inside JSON objects returned as string to keep it consistent with how they are handled at the root level.

[
  {
    "id": 1,
    "productId": 1,
    // string type from NUMERIC - fine
    "salesPrice": "20.01",
    "salesTaxRateId": 1,
    "product": {
      "id": 1,
      "name": "Marchewka",
      "description": "brak",
      "categoryId": 1
    },
    "salesTaxRate": {
      "id": 1,
      // type mismatch, table column is NUMERIC
      "value": 0.05,
      "name": "5%",
      "createdAt": "2025-03-06T16:11:38.00885"
    }
  }
]

[
  {
    "id": 1,
    "productId": 1,
    // string type from NUMERIC - fine
    "salesPrice": "20.01",
    "salesTaxRateId": 1,
    "product": {
      "id": 1,
      "name": "Marchewka",
      "description": "brak",
      "categoryId": 1
    },
    "salesTaxRate": {
      "id": 1,
      // type mismatch, table column is NUMERIC
      "value": 0.05,
      "name": "5%",
      "createdAt": "2025-03-06T16:11:38.00885"
    }
  }
]

GitHub

Issues · kysely-org/kysely

A type-safe typescript SQL query builder. Contribute to kysely-org/kysely development by creating an account on GitHub.

Solution:

So, you either need those explicit casts, or allow the DB to cast the value to JSON number and fix your types.

Jump to solution

5 Replies

tzezarOP•4w ago

to avoid XY problem I may have asked: how to have unified types for NUMERIC column type

yayza_•2w ago

I recently came a cross this as well , when a property is numeric i have to cast it to text or it will change the value to a number and i lose precision price: sql<string>${eb.ref('product.price')}::TEXT, just when i use it jsonBuildObject btw

koskimas•2w ago

There's no way for kysely to know which columns will get converted to what. Kysely has no runtime column type information whatsoever. The database + the driver are responsible for data types and conversions. Kysely never touches the output. On type level, we also don't know your string type is numeric and will get turned into a number by the database JSON functions.

Solution

koskimas•2w ago

So, you either need those explicit casts, or allow the DB to cast the value to JSON number and fix your types.

yayza_•2w ago

thank you for the clue ... realized it was a node-postgres issue. 😅

Gaming

Programming

Inconsistent Typing of NUMERIC Fields with jsonBuildObject

Did you find this page helpful?