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

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

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

问题

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

  1. CREATE TABLE IF NOT EXISTS t1 (
  2.     ID bigserial PRIMARY KEY,
  3.     name text,
  4.     lat varchar(255),
  5.     long varchar(255),
  6.     rel_id varchar(255)
  7. );

在lat和long列中，有一些值是null或空白。我想要做的是检查rel_id是否与地图项的键值匹配，并且如果lat或long为null或空白，则将其对应的值从地图中设置如下：

  1. "1708237": {40.003196, 68.766304},
  2. "1703206": {40.855638, 71.951236},
  3. "1703217": {40.789588, 71.703445},
  4. "1703209": {40.696825, 71.893556},
  5. "1703230": {40.692121, 72.072769},
  6. "1703202": {40.777208, 72.195201},
  7. "1703214": {40.912185, 72.261577},
  8. "1703232": {40.967893, 72.411201},
  9. "1703203": {40.814196, 72.464561},
  10. "1703211": {40.733920, 72.635528},
  11. "1703220": {40.769949, 72.872072},
  12. "1703236": {40.644870, 72.589310},
  13. "1703224": {40.667720, 72.237619},
  14. "1703210": {40.609053, 72.487692},
  15. "1703227": {40.522460, 72.306502},
  16. "1730212": {40.615228, 71.140965},
  17. "1730215": {40.438027, 70.528916},
  18. "1730209": {40.495830, 71.219648},
  19. "1730203": {40.463302, 71.456543},
  20. "1730224": {40.368501, 71.201116},
  21. "1730242": {40.646348, 71.658763}

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

  1. coordinates = {
  2.     "1708237": {lat: 40.003196, long: 68.766304},
  3.     "1703206": {lat: 40.855638, long: 71.951236},
  4.     "1703217": {lat: 40.789588, long: 71.703445}
  5. }
  7. for values in results {
  8.     if (values.lat == Null or values.long Null) {
  9.         values.lat = coordinates[values.rel_id].lat;
  10.         values.long = coordinates[values.rel_id].long;
  11.     }
  12. }

我需要在SQL中实现这样的逻辑。谢谢。

我尝试了以下方法，但无法使其工作：

  1. DO $$
  2.     DECLARE
  3.         coordinates jsonb := '[
  4.           {
  5.             "rel_id": "1",
  6.             "lat": 1231.123,
  7.             "long": 1423.2342
  8.           },
  9.           {
  10.             "rel_id": "2",
  11.             "lat": 1231.123,
  12.             "long": 1423.2342
  13.           },
  14.           {
  15.             "rel_id": "3",
  16.             "lat": 1231.123,
  17.             "long": 1423.2342
  18.           },
  19.           {
  20.             "rel_id": "4",
  21.             "lat": 1231.123,
  22.             "long": 1423.2342
  23.           },
  24.           {
  25.             "rel_id": "5",
  26.             "lat": 1231.123,
  27.             "long": 1423.2342
  28.           },
  29.           {
  30.             "rel_id": "6",
  31.             "lat": 1231.123,
  32.             "long": 1423.2342
  33.           },
  34.           {
  35.             "rel_id": "7",
  36.             "lat": 1231.123,
  37.             "long": 1423.2342
  38.           },
  39.           {
  40.             "rel_id": "8",
  41.             "lat": 1231.123,
  42.             "long": 1423.2342
  43.           }
  44.         ]'::jsonb;
  45.     BEGIN
  47.         UPDATE t1
  48.         SET lat = ci.clat,
  49.             long = ci.clong
  50.         FROM (SELECT json_array_elements(coordinates) ->> 'rel_id' AS crel_id, 'lat' AS clat, 'long' AS clong) AS ci
  51.         WHERE t1.rel_id = ci.crel_id AND (t1.long IS NULL OR t1.lat IS NULL);
  52.     END
  53. $$;

我有一个常量坐标的JSON映射。我尝试的是，当记录的rel_id与coordinates映射中的id匹配且lat或long的值为空时，更新lat和long的值。

英文:

I have a following like table

  1. CREATE TABLE
  2.     IF NOT EXISTS t1
  3. (
  4.     ID                bigserial PRIMARY KEY,
  5.     name              text,
  6.     lat               varchar(255),
  7.     long              varchar(255),
  8.     rel_id varchar(255)
  9. );

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:

  1. "1708237": {40.003196, 68.766304},
  2. "1703206": {40.855638, 71.951236},
  3. "1703217": {40.789588, 71.703445},
  4. "1703209": {40.696825, 71.893556},
  5. "1703230": {40.692121, 72.072769},
  6. "1703202": {40.777208, 72.195201},
  7. "1703214": {40.912185, 72.261577},
  8. "1703232": {40.967893, 72.411201},
  9. "1703203": {40.814196, 72.464561},
  10. "1703211": {40.733920, 72.635528},
  11. "1703220": {40.769949, 72.872072},
  12. "1703236": {40.644870, 72.589310},
  13. "1703224": {40.667720, 72.237619},
  14. "1703210": {40.609053, 72.487692},
  15. "1703227": {40.522460, 72.306502},
  16. "1730212": {40.615228, 71.140965},
  17. "1730215": {40.438027, 70.528916},
  18. "1730209": {40.495830, 71.219648},
  19. "1730203": {40.463302, 71.456543},
  20. "1730224": {40.368501, 71.201116},
  21. "1730242": {40.646348, 71.658763},

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

  1. coordinates = {
  2. "1708237": {lat: 40.003196,long: 68.766304},
  3. "1703206": {lat: 40.855638,long: 71.951236},
  4. "1703217": {lat: 40.789588,long: 71.703445}
  5. }
  7. for values in results {
  8.     if (values.lat == Null or values.long Null) {
  9.         values.lat = coordinates[values.rel_id].lat;
  10.         values.long = coordinates[values.rel_id].long;
  11.     }
  12. }

I need such logic in SQL. Thank you.

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

  1. DO
  2. $$
  3.     DECLARE
  4.         coordinates jsonb := '[
  5.           {
  6.             "rel_id": "1",
  7.             "lat": 1231.123,
  8.             "long": 1423.2342
  9.           },
  10.           {
  11.             "rel_id": "2",
  12.             "lat": 1231.123,
  13.             "long": 1423.2342
  14.           },
  15.           {
  16.             "rel_id": "3",
  17.             "lat": 1231.123,
  18.             "long": 1423.2342
  19.           },
  20.           {
  21.             "rel_id": "4",
  22.             "lat": 1231.123,
  23.             "long": 1423.2342
  24.           },
  25.           {
  26.             "rel_id": "5",
  27.             "lat": 1231.123,
  28.             "long": 1423.2342
  29.           },
  30.           {
  31.             "rel_id": "6",
  32.             "lat": 1231.123,
  33.             "long": 1423.2342
  34.           },
  35.           {
  36.             "rel_id": "7",
  37.             "lat": 1231.123,
  38.             "long": 1423.2342
  39.           },
  40.           {
  41.             "rel_id": "8",
  42.             "lat": 1231.123,
  43.             "long": 1423.2342
  44.           }
  45.         ]'::jsonb;
  46.     BEGIN
  48.         UPDATE t1
  49.         SET lat  = ci.clat,
  50.             long = ci.clong
  51.         from (select json_array_elements(coordinates) ->> 'rel_id' as crel_id, 'lat' as clat, 'long' as clong) as ci
  52.         WHERE t1.rel_id = ci.crel_id && (t1.long is NULL or t1.lat is NULL);
  53.     END
  54. $$;

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_id、clat和clong的值转换为单独的列。我认为这是查询中最棘手的部分。
  2. 使用json_data表中的值更新t1表。如果t1.lat或t1.long为空或为null，则更新值。

最终的过程可能是：

  1. DO
  2. $$
  3.     DECLARE
  4.         coordinates jsonb := '[
  5.           {
  6.             "rel_id": "1708237",
  7.             "lat": 1,
  8.             "long": 2
  9.           },
  10.           {
  11.             "rel_id": "1703206",
  12.             "lat": 3,
  13.             "long": 4
  14.           },
  15.           {
  16.             "rel_id": "1703217",
  17.             "lat": 5,
  18.             "long": 6
  19.           },
  20.           {
  21.             "rel_id": "1703209",
  22.             "lat": 7,
  23.             "long": 8
  24.           },
  25.           {
  26.             "rel_id": "1703230",
  27.             "lat": 9,
  28.             "long": 10
  29.           }
  30.         ]'::jsonb;
  31.     BEGIN
  33. 		update t1
  34. 		set lat  = json_data.clat,
  35. 		    long = json_data.clong
  36. 		from (select 
  37. 		         coords->>'rel_id' as crel_id, --将jsonb值转换为字符串
  38. 		         coords->>'lat' as clat,
  39. 		         coords->>'long' as clong
  40.               from (select jsonb_array_elements(coordinates) as coords) 
  41.               as coords_data)
  42.         as json_data
  43. 		where t1.rel_id = json_data.crel_id and
  44. 		  -- 检查'lat'和'long'列的值可能为null或为空
  45. 		  (t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
  46.     END
  47. $$;
英文:

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:

  1. DO
  2. $$
  3.     DECLARE
  4.         coordinates jsonb := '[
  5.           {
  6.             "rel_id": "1708237",
  7.             "lat": 1,
  8.             "long": 2
  9.           },
  10.           {
  11.             "rel_id": "1703206",
  12.             "lat": 3,
  13.             "long": 4
  14.           },
  15.           {
  16.             "rel_id": "1703217",
  17.             "lat": 5,
  18.             "long": 6
  19.           },
  20.           {
  21.             "rel_id": "1703209",
  22.             "lat": 7,
  23.             "long": 8
  24.           },
  25.           {
  26.             "rel_id": "1703230",
  27.             "lat": 9,
  28.             "long": 10
  29.           }
  30.         ]'::jsonb;
  31.     BEGIN
  33. 		update t1
  34. 		set lat  = json_data.clat,
  35. 		    long = json_data.clong
  36. 		from (select 
  37. 		         coords->'rel_id' #>> '{}' as crel_id, --convert jsonb value to string
  38. 		         coords->'lat' as clat,
  39. 		         coords->'long' as clong
  40.               from (select jsonb_array_elements(coordinates) as coords) 
  41.               as coords_data)
  42.         as json_data
  43. 		where t1.rel_id = json_data.crel_id and
  44. 		  -- check value may be null or empty for both 'lat' and 'long' columns
  45. 		  (t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
  46.     END
  47. $$;

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

发表评论

匿名网友

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

确定