Kysely•14mo ago

Join using a json value with postgres

I have this table:

export interface MzCatalogMzAuditEvents {
  id: Generated<string>;
  event_type: Generated<string>;
  object_type: Generated<string>;
  details: Generated<Json>;
  user: Generated<string | null>;
  occurred_at: Generated<Timestamp>;
}

export interface MzCatalogMzAuditEvents {
  id: Generated<string>;
  event_type: Generated<string>;
  object_type: Generated<string>;
  details: Generated<Json>;
  user: Generated<string | null>;
  occurred_at: Generated<Timestamp>;
}

I'm trying to write this join:

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(eb.ref("ae.details", "->>").key("id"), "=", "o.id")
  )
)

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(eb.ref("ae.details", "->>").key("id"), "=", "o.id")
  )
)

But I get this error on the key(id):

Argument of type 'string' is not assignable to parameter of type 'never'.

Argument of type 'string' is not assignable to parameter of type 'never'.

and this one the o.id:

Argument of type '"o.id"' is not assignable to parameter of type 'OperandValueExpressionOrList<{ "mz_catalog.mz_array_types": MzCatalogMzArrayTypes; "mz_catalog.mz_audit_events": MzCatalogMzAuditEvents; "mz_catalog.mz_aws_privatelink_connections": MzCatalogMzAwsPrivatelinkConnections; ... 121 more ...; ae: MzCatalogMzAuditEvents; }, "o" | ... 2 more ... | "ae", TraversedJSONPathBu...'. (tsserver 2345)

Argument of type '"o.id"' is not assignable to parameter of type 'OperandValueExpressionOrList<{ "mz_catalog.mz_array_types": MzCatalogMzArrayTypes; "mz_catalog.mz_audit_events": MzCatalogMzAuditEvents; "mz_catalog.mz_aws_privatelink_connections": MzCatalogMzAwsPrivatelinkConnections; ... 121 more ...; ae: MzCatalogMzAuditEvents; }, "o" | ... 2 more ... | "ae", TraversedJSONPathBu...'. (tsserver 2345)

12 Replies

koskimas•14mo ago

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(
      eb.ref("ae.details", "->>").key("id"),
      "=",
      eb.ref("o.id"),
     )
  )
)

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(
      eb.ref("ae.details", "->>").key("id"),
      "=",
      eb.ref("o.id"),
     )
  )
)

Or alternatively

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(
      "o.id",
      "=",
      eb.ref("ae.details", "->>").key("id"),
     )
  )
)

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(
      "o.id",
      "=",
      eb.ref("ae.details", "->>").key("id"),
     )
  )
)

But it seems that the details object doesn't have an column id in your types.

Robin•14mo ago

yeah, the generated type seems to be just generic json

koskimas•14mo ago

Yeah the type generator can't know the shape of the JSON object. What kind of type does the generator generate?

Robin•14mo ago

export type Json = ColumnType<JsonValue, string, string>;

export type JsonArray = JsonValue[];

export type JsonObject = {
  [K in string]?: JsonValue;
};

export type JsonPrimitive = boolean | null | number | string;

export type JsonValue = JsonArray | JsonObject | JsonPrimitive;

export type Json = ColumnType<JsonValue, string, string>;

export type JsonArray = JsonValue[];

export type JsonObject = {
  [K in string]?: JsonValue;
};

export type JsonPrimitive = boolean | null | number | string;

export type JsonValue = JsonArray | JsonObject | JsonPrimitive;

so just any json data which makes sense I guess, but how do people do this generally? A hand-rolled type for the json?

Robin•14mo ago

looks like there is an open issue: https://github.com/RobinBlomberg/kysely-codegen/issues/75

GitHub

Define types for JSON columns · Issue #75 · RobinBlomberg/kysely-co...

Hey!, First thanks for creating this tool - saves time for sure! This is probably similar to #30 However that seemed focused on deriving the types from ENUMs and headed down a different path. I was...

koskimas•14mo ago

Oh crap.. Yeah that's not compatible with the JSON reference builder

Robin•14mo ago

so for this to work, you would have to manually define the type, yeah?

koskimas•14mo ago

For now you can use this:

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(
      "o.id",
      "=",
      sql`${eb.ref("ae.details")}->>'id'`,
     )
  )
)

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(
      "o.id",
      "=",
      sql`${eb.ref("ae.details")}->>'id'`,
     )
  )
)

Robin•14mo ago

cool, that works, thanks! I don't know much about json in postgres, do json columns have a schema?

koskimas•14mo ago

Nope They are just opaque blobs I think it would actually be better if the type generator typed them as any

Robin•14mo ago

so the only way to support this with codegen is have some way of passing in a type of json objects hmm

koskimas•14mo ago

That'd also work with kysely Or unknown We could make that work

Gaming

Programming

Join using a json value with postgres