如何在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. );

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

  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. }
  6. for values in results {
  7. if (values.lat == Null or values.long Null) {
  8. values.lat = coordinates[values.rel_id].lat;
  9. values.long = coordinates[values.rel_id].long;
  10. }
  11. }

我需要在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
  46. UPDATE t1
  47. SET lat = ci.clat,
  48. long = ci.clong
  49. FROM (SELECT json_array_elements(coordinates) ->> 'rel_id' AS crel_id, 'lat' AS clat, 'long' AS clong) AS ci
  50. WHERE t1.rel_id = ci.crel_id AND (t1.long IS NULL OR t1.lat IS NULL);
  51. END
  52. $$;

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

英文:

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. }
  6. for values in results {
  7. if (values.lat == Null or values.long Null) {
  8. values.lat = coordinates[values.rel_id].lat;
  9. values.long = coordinates[values.rel_id].long;
  10. }
  11. }

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
  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 && (t1.long is NULL or t1.lat is NULL);
  52. END
  53. $$;

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,则更新值。

最终的过程可能是:

  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
  32. update t1
  33. set lat = json_data.clat,
  34. long = json_data.clong
  35. from (select
  36. coords->>'rel_id' as crel_id, --将jsonb值转换为字符串
  37. coords->>'lat' as clat,
  38. coords->>'long' as clong
  39. from (select jsonb_array_elements(coordinates) as coords)
  40. as coords_data)
  41. as json_data
  42. where t1.rel_id = json_data.crel_id and
  43. -- 检查'lat''long'列的值可能为null或为空
  44. (t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
  45. END
  46. $$;
英文:

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
  32. update t1
  33. set lat = json_data.clat,
  34. long = json_data.clong
  35. from (select
  36. coords->'rel_id' #>> '{}' as crel_id, --convert jsonb value to string
  37. coords->'lat' as clat,
  38. coords->'long' as clong
  39. from (select jsonb_array_elements(coordinates) as coords)
  40. as coords_data)
  41. as json_data
  42. where t1.rel_id = json_data.crel_id and
  43. -- check value may be null or empty for both 'lat' and 'long' columns
  44. (t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
  45. END
  46. $$;

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:

确定