在Postgres中更新json字段

8 浏览
0 Comments

在Postgres中更新json字段

使用Postgres 9.3通过json字段查询非常好。\n然而，我找不到一种正式的方法来更新json对象，所以我使用了一个基于先前帖子的内部函数，该函数使用plpythonu编写（参考链接：How do I modify fields inside the new PostgreSQL JSON datatype?）：\nCREATE OR REPLACE FUNCTION json_update(data json, key text, value json)\nRETURNS json AS\n$BODY$\n from json import loads, dumps\n if key is None: return data\n js = loads(data)\n js[key] = value\n return dumps(js)\n$BODY$\nLANGUAGE plpythonu VOLATILE\n\n当我的json更新保持扁平和简单时，它工作得非常好。例如，\"GO_SESSION\"表中的\"chat\"是一个json类型字段，包含{\"a\":\"1\",\"b\":\"2\"}，以下代码将更改\'b\'的值，并将\"chat\"变为{\"a\":\"1\",\"b\":\"5\"}：\n

update "GO_SESSION" set chat=json_update(chat,'b','5') where id=3

\n问题是，当我尝试将\'b\'赋值为一个对象而不是一个简单的值时：\n

update "GO_SESSION" set chat=json_update(chat,'b','{"name":"steve"}') where id=3

\n数据库中的结果是，\'b\'包含一个转义的字符串，而不是一个真正的json对象：\n{\"a\": \"1\", \"b\": \"{\\\"name\\\":\\\"steve\\\"}\"}\n我尝试了不同的方法来取消转义或转储我的json，以保持\'b\'为一个对象，但是没有找到解决办法。\n谢谢。

0

3 答案

0 Comments

在使用plv8（可在Heroku等服务上使用的受信任的语言）的人们中，经常需要对json字段进行迁移或更新，直接在数据库上运行查询要比下载所有数据、转换数据然后进行更新要快得多。

问题的解决方法是创建一个名为json_replace_string的函数，它使用plv8语言编写。该函数接受四个参数：obj（json对象），path（json对象的路径），value（要更新的值），force（是否强制创建路径）。函数首先检查value是否为null且force参数为false，如果是，则返回原始的json对象。然后，函数使用正则表达式对路径进行处理，并根据路径逐层更新json对象的值。最后，函数返回更新后的json对象。

使用该函数的方法是使用UPDATE语句，将json_replace_string函数应用于要更新的json字段。其中，参数blob是要更新的json字段，'some.nested.path[5].to.object'是要更新的路径，'new value'是要更新的值，false是force参数的值。同时，WHERE子句可以根据需要添加条件。

force参数有两个功能：（1）允许设置null值。如果根据不存在的其他列动态生成值，例如blob->'non_existent_value'，则null将作为函数的输入，您可能不希望将值设置为null。（2）强制在要更改的json对象中创建嵌套路径，如果路径不存在的话。例如，json_replace_string函数中的例子给出了一个json对象和一个要添加的键值对，当force参数为true时，将创建一个新的键值对。

可以想象类似的函数来更新数值、删除键等。这基本上在postgres中实现了类似mongo的功能，用于快速原型设计和在架构稳定之前，将数据存储在json字段中，之后再将其拆分到独立的列和表中，以获得最佳性能。

0
0 Comments

问题出现的原因是上述的plpythonu函数将"value"作为字符串处理，而不管它实际上是一个复杂的json对象。解决问题的关键是在value周围添加eval()：

js[key] = eval(value)

这样，json字符串（在此示例中命名为'value'）将失去其外部的双引号"{...}"，变成一个对象。

这根本不应该是必要的。

... 实际上并不是这样，可以看到我的答案。如果你发现自己在使用eval，请考虑一下“哎呀，有没有更安全的方法”。eval()几乎从来不是正确的答案。

0
0 Comments

问题的原因是在更新Postgres中的json字段时，没有正确解码json对象。解决方法是使用loads函数对值进行解码，而不是使用eval函数。此外，还要注意不要使用eval函数，因为它是不可靠和危险的。

以下是解决问题的代码示例：

CREATE OR REPLACE FUNCTION json_update(data json, key text, value json)
RETURNS json AS
$BODY$
   from json import loads, dumps
   if key is None: return data
   js = loads(data)
   # you must decode 'value' with loads too:
   js[key] = loads(value)
   return dumps(js)
$BODY$
LANGUAGE plpythonu VOLATILE;
postgres=# SELECT json_update('{"a":1}', 'a', '{"innerkey":"innervalue"}');
            json_update            
-----------------------------------
 {"a": {"innerkey": "innervalue"}}
(1 row)

另外，使用eval函数解码json是不可靠和危险的。它不可靠是因为json不是Python语言，它只是在大多数情况下类似于Python的语法。它是不安全的是因为你永远不知道你可能会eval的内容。在这种情况下，你大部分受到PostgreSQL的json解析器的保护：

postgres=# SELECT json_update(
postgres(#    '{"a":1}', 
postgres(#    'a', 
postgres(#    '__import__(''shutil'').rmtree(''/glad_this_is_not_just_root'')'
postgres(# );
ERROR:  invalid input syntax for type json
LINE 4:          '__import__(''shutil'').rmtree(''/glad_this_is_not_...
                 ^
DETAIL:  Token "__import__" is invalid.
CONTEXT:  JSON data, line 1: __import__...

但如果有人能够绕过这个限制，成功地使用eval函数进行攻击，我也不会感到意外。所以，这里的教训是：不要使用eval函数。

0