如何在PostgreSQL中从映射(关联数组)中设置值?

huangapple go评论72阅读模式
英文:

How to set values from map (associative array) of items in postgresql?

问题

我有一个类似于以下表格的表:

CREATE TABLE IF NOT EXISTS t1 (
    ID bigserial PRIMARY KEY,
    name text,
    lat varchar(255),
    long varchar(255),
    rel_id varchar(255)
);

latlong列中,有一些值是null或空白。我想要做的是检查rel_id是否与地图项的键值匹配,并且如果latlongnull或空白,则将其对应的值从地图中设置如下:

"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}

这样就不会在表中出现null值。伪代码如下:

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 AND (t1.long IS NULL OR t1.lat IS NULL);
    END
$$;

我有一个常量坐标的JSON映射。我尝试的是,当记录的rel_idcoordinates映射中的id匹配且latlong的值为空时,更新latlong的值。

英文:

I have a following like table

CREATE TABLE
    IF NOT EXISTS t1
(
    ID                bigserial PRIMARY KEY,
    name              text,
    lat               varchar(255),
    long              varchar(255),
    rel_id varchar(255)
);

Here some values in lat, long columns are null or blank. What I want to do is to check if rel_id matches key value of map items and if lat or long is null or blank set its corresponding value from map like this:

"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},

so that there won't be null values in table. Pseudocode might look like this:

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;
    }
}

I need such logic in SQL. Thank you.

What I tried was, but I can't make it work:

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
$$;

I have a constant map of coordinates in json. What I'm trying to do is update lat and long values where rel_id of records match with id in coordinates map and lat or long values are null.

答案1

得分: 2

DB fiddle

步骤:

  1. 读取输入的jsonb并将crel_idclatclong的值转换为单独的列。我认为这是查询中最棘手的部分。
  2. 使用json_data表中的值更新t1表。如果t1.latt1.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, --将jsonb值转换为字符串
		         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
		  -- 检查'lat'和'long'列的值可能为null或为空
		  (t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
    END
$$;
英文:

DB fiddle

Steps:

  1. Read input jsonb and convert crel_id, clat and clong values into separate columns. I would say that this is the trickiest part of the query.
  2. Update t1 table using values from json_data table. Update values if there is t1.lat or t1.long is empty or null.

Final procedure could be:

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
$$;

huangapple
  • 本文由 发表于 2023年7月21日 12:08:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76735060.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定