时间戳

首页 > 解决方案 > 将 postgres 表与 json 数据同步

sql - 将 postgres 表与 json 数据同步

问题描述

我有一个端点来获取 json 格式的信息

[
  {
    id: 123,
    name: 'a name',
    code: '123SDF',
    category: 'some category'
  },
  {
    id: 234,
    name: 'a another name',
    code: '234SDF',
    category: 'some category'
  },
  etc...
]

我需要将它与我拥有的本地表同步。我只是截断和添加，但现在我需要更新或插入。原因是因为我正在向本地表添加列。本地表有这些列：

id,
name,
code,
category,
template

所以我不能再截断，因为我会丢失模板字段。我可以运行什么查询，以便在找到 id 时更新，否则为我的额外字段插入 null？

json 数据和我的表的 id 将相同。

标签： sqlpostgresql

解决方案

我想有多种方法可以做到这一点。您可以配置您的表以使该id列唯一，然后ON CONFLICT在该行已经存在时使用一个子句进行更新。

但是，这是一种无需更改您的桌子的任何内容的方法。

  1. 转换数据（只是让事情变得更容易）
  2. 执行 UPDATE 并找出更新了哪些 id
  3. 对未更新的任何 id 执行 INSERT

设置：

CREATE TABLE t (id INTEGER, name TEXT, code TEXT, category TEXT, template TEXT);

INSERT INTO t VALUES (123, 'a name', 'a code', 'a cat', 'a template');

穷人的告白：

WITH
  source_data AS (
    SELECT (j->>'id')::INTEGER AS id,
           j->>'name' AS name,
           j->>'code' AS code,
           j->>'category' AS category
    FROM JSON_ARRAY_ELEMENTS(
      '[
        {
        "id": 123,
        "name": "a name",
        "code": "123SDF",
        "category": "some category"
        },
        {
        "id": 234,
        "name": "a another name",
        "code": "234SDF",
        "category": "some category"
        }
      ]'::JSON) j
  ),
  updated AS (
    UPDATE t
    SET name = s.name,
        code = s.code,
        category = s.category
    FROM source_data s
    WHERE t.id = s.id
    RETURNING t.id
  )
INSERT INTO t
SELECT id, name, code, category, NULL
FROM source_data s
WHERE s.id NOT IN (SELECT id FROM updated);

SELECT * FROM t在 upsert 之后执行的结果：

| id  | name           | code   | category      | template   |
| --- | -------------- | ------ | ------------- | ---------- |
| 123 | a name         | 123SDF | some category | a template |
| 234 | a another name | 234SDF | some category |            |

您可以看到现有记录的模板值没有被覆盖。

推荐阅读