使用节点插入JSON对象，然后使用节点，而无需forloop。

Question

我有一系列对象，例如与下面的对象相同的设置。我想将这些对象数组插入看起来像这样的Postgres表中：[！[table Setup] [1]] [1] 我试图在下面的函数中发挥功能，但是在插入ungdomskole时它会返回错误，因为这是一个字符串，因此它不了解空间，因此它在第二个输入值上崩溃。我该如何理解它是字符串？

{
  '@type': 'SensorSystem',
  id: 'SN47230',
  name: 'ÅKRA UNGDOMSSKOLE',
  shortName: 'Åkra ',
  country: 'Norge',
  countryCode: 'NO',
  geometry: {
    '@type': 'Point',
    coordinates: [ 5.1963, 59.2555 ],
    nearest: false
  },
  masl: 18,
  validFrom: '2013-10-29T00:00:00.000Z',
  county: 'ROGALAND',
  countyId: 11,
  municipality: 'KARMØY',
  municipalityId: 1149,
  stationHolders: [ 'KARMØY KOMMUNE' ],
  externalIds: [ '506131077' ],
  wigosId: '0-578-0-47230'
}

错误代码：

error: syntax error at or near "UNGDOMSSKOLE"

到目前为止我尝试过的是：

let sqlinsert= data.data.map((source)=>{
          if (source.geometry) {
            if(!source.masl){
              source.masl=0
          }
          let Point = `POINT(${source.geometry.coordinates[0]} ${source.geometry.coordinates[1]})`;
          return `(${source.id}, ${source.name}, ${source.shortName},${source.country},${source.countryCode},${source.masl},${source.geometry.coordinates[0]},${source.geometry.coordinates[1]},${Point},${source.validFrom},${source.county},${source.countyId},${source.municipality},${source.municipalityId})`
          }
        })
const result = await db.query("INSERT INTO sources(source_id,name,shortName,country,countryCode,masl,long,lat,geog,valid_from,county,countyId,municipality,municipalityId) values"+sqlinsert[0])

我对此遇到的第二个问题是，插入

POINT(59.2555 5.1963)

给出了5.1963的语法错误 [1]： https://i.sstatic.net/4rskq.png

Answer 1

书面查询的主要问题是，您正在将原始的，未设计的值添加到您的值记录中。您可以在数据库客户端上使用 Escapeliteral ，以确保正确逃脱了这些值，这将解决您所获得的语法错误：

    const data = [
      {
        "@type": "SensorSystem",
        id: "SN47230",
        name: "ÅKRA UNGDOMSSKOLE",
        shortName: "Åkra ",
        country: "Norge",
        countryCode: "NO",
        geometry: {
          "@type": "Point",
          coordinates: [5.1963, 59.2555],
          nearest: false,
        },
        masl: 18,
        validFrom: "2013-10-29T00:00:00.000Z",
        county: "ROGALAND",
        countyId: 11,
        municipality: "KARMØY",
        municipalityId: 1149,
        stationHolders: ["KARMØY KOMMUNE"],
        externalIds: ["506131077"],
        wigosId: "0-578-0-47230",
      },
    ].map((source) => {
      const {
        id,
        name,
        shortName,
        country,
        countryCode,
        masl,
        geometry: {
          // the coordinates in your source data appear to be in y,x instead of
          // x,y. Treating them as x,y results in the point being located
          // in the Indian Ocean while y,x is somewhere in Norway.
          coordinates: [lat, long],
        },
        validFrom,
        county,
        countyId,
        municipality,
        municipalityId,
      } = source;

      return [
        id,
        name,
        shortName,
        country,
        countryCode,
        masl || 0,
        long,
        lat,
        `POINT( ${long} ${lat} )`,
        validFrom,
        county,
        countyId,
        municipality,
        municipalityId,
      ];
    });

    const headers = [
      "source_id",
      "name",
      "shortname",
      "country",
      "countrycode",
      "masl",
      "long",
      "lat",
      "geog",
      "valid_from",
      "county",
      "countyid",
      "municipality",
      "municipalityid",
    ];

    const sourceValStr = data
      .map((sourceRecords, rowIndex) => {
        return sourceRecords
          .map((value, colIndex) => {
            if (typeof value === "string") {
              // safely escape string values
              return dbClient.escapeLiteral(value);
            }
            if (
              typeof value === "number" ||
              typeof value === "boolean" ||
              typeof value === "bigint"
            ) {
              return value;
            }

            if (value === undefined || value === null) {
              return "null";
            }

            throw new Error(
              `non-simple value: ${JSON.stringify(value)} for ${
                headers[colIndex]
              } at row ${rowIndex}`
            );
          })
          .join(",");
      })
      .map((value) => `(${value})`)
      .join(",");

    const sourceInsert = `INSERT INTO sources(${headers.join(
      ","
    )}) VALUES ${sourceValStr};`;
    await dbClient.query(sourceInsert);

插入行更有效，更可扩展的方法是使用 pg-copy-streams 与csv库相结合的库，例如 csv-stringify 它将使用 复制从 流：

import { from as copyFrom } from "pg-copy-streams";
import { stringify } from "csv-stringify";
// ...
const copyStmt = `COPY sources(${headers.join(
      ","
    )}) FROM STDIN (FORMAT CSV)`;
await new Promise<void>((resolve, reject) => {
      const copyStream = dbClient.query(copyFrom(copyStmt));
      const stringifiedStream = stringify(data, {
        header: false,
        encoding: "utf-8",
        delimiter: ",",
        quote: "\"",
      });
      stringifiedStream
        .on("error", (err) => {
          reject(err);
        })
        .on("end", () => resolve());
      stringifiedStream.pipe(copyStream);
    });

在我的低端笔记本电脑上，此方法大约需要39秒才能插入一百万行没有数据库优化。