Custom JSON · Clickhouse Js Node Coding · ClickHouse/agent-skills · Skills Docs

Custom JSON · Clickhouse Js Node Coding · ClickHouse/agent-skills · Skills Docs

import { createClient } from "@clickhouse/client";

const valueSerializer = (value: unknown): unknown => {
  // Serialize Date as a UNIX millis number (instead of toJSON's ISO string)
  if (value instanceof Date) {
    return value.getTime();
  }

  // Serialize BigInt as a string so JSON.stringify won't throw
  if (typeof value === "bigint") {
    return value.toString();
  }

  if (Array.isArray(value)) {
    return value.map(valueSerializer);
  }

  if (typeof value === "object" && value !== null) {
    return Object.fromEntries(
      Object.entries(value).map(([k, v]) => [k, valueSerializer(v)]),
    );
  }

  return value;
};

const client = createClient({
  json: {
    parse: JSON.parse, // use default parsing
    stringify: (obj: unknown) => JSON.stringify(valueSerializer(obj)),
  },
});

await client.command({
  query: `
    CREATE OR REPLACE TABLE inserts_custom_json_handling
    (id UInt64, dt DateTime64(3, 'UTC'))
    ENGINE MergeTree
    ORDER BY id
  `,
});

await client.insert({
  table: "inserts_custom_json_handling",
  format: "JSONEachRow",
  values: [
    {
      id: BigInt("250000000000000200"), // serialized as a string
      dt: new Date(), // serialized as ms since epoch
    },
  ],
});

await client.close();

import { createClient } from "@clickhouse/client";
import JSONBig from "json-bigint";

const bigJson = JSONBig({ useNativeBigInt: true });

const client = createClient({
  json: {
    parse: bigJson.parse,
    stringify: bigJson.stringify,
  },
  clickhouse_settings: {
    output_format_json_quote_64bit_integers: 0,
  },
});

import { createClient } from "@clickhouse/client";

const parseBigInt = (text: string) =>
  JSON.parse(text, function (key, value, context) {
    if (key.endsWith("__bigint")) {
      return BigInt(context.source);
    }
    return value;
  });

const client = createClient({
  json: {
    parse: parseBigInt,
    stringify: JSON.stringify, // use default stringify
  },
  clickhouse_settings: { output_format_json_quote_64bit_integers: 0 },
});

const rs = await client.query({
  query: "SELECT toUInt64(250000000000000200) AS id__bigint",
});
const { data } = await rs.json();
console.log(data[0].id__bigint); // 250000000000000200

await client.close();