在PostgreSQL中如何为项目映射（关联数组）设置值？

从现在开始，我们要努力学习啦！今天我给大家带来《在PostgreSQL中如何为项目映射（关联数组）设置值？》，感兴趣的朋友请继续看下去吧！下文中的内容我们主要会涉及到等等知识点，如果在阅读本文过程中有遇到不清楚的地方，欢迎留言呀！我们一起讨论，一起学习！

我有一个类似的表格

create table
    if not exists t1
(
    id                bigserial primary key,
    name              text,
    lat               varchar(255),
    long              varchar(255),
    rel_id varchar(255)
);

此处纬度、长列中的某些值为空或空白。我想要做的是检查 rel_id 是否与地图项的键值匹配，以及纬度或经度是否为空或空白，从地图中设置其相应的值，如下所示：

"1708237": {40.003196, 68.766304},
"1703206": {40.855638, 71.951236},
"1703217": {40.789588, 71.703445},
"1703209": {40.696825, 71.893556},
"1703230": {40.692121, 72.072769},
"1703202": {40.777208, 72.195201},
"1703214": {40.912185, 72.261577},
"1703232": {40.967893, 72.411201},
"1703203": {40.814196, 72.464561},
"1703211": {40.733920, 72.635528},
"1703220": {40.769949, 72.872072},
"1703236": {40.644870, 72.589310},
"1703224": {40.667720, 72.237619},
"1703210": {40.609053, 72.487692},
"1703227": {40.522460, 72.306502},
"1730212": {40.615228, 71.140965},
"1730215": {40.438027, 70.528916},
"1730209": {40.495830, 71.219648},
"1730203": {40.463302, 71.456543},
"1730224": {40.368501, 71.201116},
"1730242": {40.646348, 71.658763},

这样表中就不会出现空值。伪代码可能如下所示：

coordinates = {
"1708237": {lat: 40.003196,long: 68.766304},
"1703206": {lat: 40.855638,long: 71.951236},
"1703217": {lat: 40.789588,long: 71.703445}
}

for values in results {
    if (values.lat == null or values.long null) {
        values.lat = coordinates[values.rel_id].lat;
        values.long = coordinates[values.rel_id].long;
    }
}

我需要这样的 sql 逻辑。谢谢。

我尝试过，但无法使其工作：

DO
$$
    DECLARE
        coordinates jsonb := '[
          {
            "rel_id": "1",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "2",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "3",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "4",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "5",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "6",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "7",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "8",
            "lat": 1231.123,
            "long": 1423.2342
          }
        ]'::jsonb;
    BEGIN

        UPDATE t1
        SET lat  = ci.clat,
            long = ci.clong
        from (select json_array_elements(coordinates) ->> 'rel_id' as crel_id, 'lat' as clat, 'long' as clong) as ci
        WHERE t1.rel_id = ci.crel_id && (t1.long is NULL or t1.lat is NULL);
    END
$$;

我有一个 json 格式的常量坐标图。我想做的是更新纬度和经度值，其中记录的 rel_id 与坐标地图中的 id 匹配，并且纬度或经度值为空。

正确答案

DB fiddle

步骤：

1. 读取输入 jsonb 并将 crel_id、clat 和 clong 值转换为单独的列。我想说这是查询中最棘手的部分。
2. 使用 json_data 表中的值更新 t1 表。如果 t1.lat 或 t1.long 为空或为 null，则更新值。

最终程序可能是：

DO
$$
    DECLARE
        coordinates jsonb := '[
          {
            "rel_id": "1708237",
            "lat": 1,
            "long": 2
          },
          {
            "rel_id": "1703206",
            "lat": 3,
            "long": 4
          },
          {
            "rel_id": "1703217",
            "lat": 5,
            "long": 6
          },
          {
            "rel_id": "1703209",
            "lat": 7,
            "long": 8
          },
          {
            "rel_id": "1703230",
            "lat": 9,
            "long": 10
          }
        ]'::jsonb;
    BEGIN

        update t1
        set lat  = json_data.clat,
            long = json_data.clong
        from (select 
                 coords->'rel_id' #>> '{}' as crel_id, --convert jsonb value to string
                 coords->'lat' as clat,
                 coords->'long' as clong
              from (select jsonb_array_elements(coordinates) as coords) 
              as coords_data)
        as json_data
        where t1.rel_id = json_data.crel_id and
          -- check value may be null or empty for both 'lat' and 'long' columns
          (t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
    END
$$;

今天关于《在PostgreSQL中如何为项目映射（关联数组）设置值？》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于的内容请关注golang学习网公众号！