postgresql 文本到 json 的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25871477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql text to json conversion
提问by peterko
I have in the database a table query_config, which will contain scripts for querying, as well as other configuration values??. This is the reason why I use json type. Table as follows:
我在数据库中有一个表query_config，其中将包含用于查询的脚本以及其他配置值？？。这就是我使用 json 类型的原因。表如下：
CREATE TABLE query_config
(
?? id integer,
?? execute_query json
);
In this table I want to insert eg:
在这个表中，我想插入例如：
INSERT INTO query_config(id, execute_query)
VALUES (4, ('{"query": ' ||
'"select *
from tests
where tests.id > 5
order by moment desc"}')::json);
But I keep getting the error:
但我不断收到错误消息：
ERROR: invalid input syntax for type json
DETAIL: Character with value 0x0a must be escaped.
What am I doing wrong please and how do I escape newline character?
请问我做错了什么，如何转义换行符？
采纳答案by cur4so
it will work if you remove new lines, in other words, put the json value on a single line like:
如果您删除新行，它将起作用，换句话说，将 json 值放在一行中，例如：
INSERT INTO query_config(id, execute_query)
VALUES (4, ('{"query":' || '"select * from tests where tests.id > 5 order by moment desc"}')::json);
回答by Clodoaldo Neto
Use to_jsonto make it valid json and dollar quotingso it will swallow any text in the query
使用to_json，使其有效的JSON，并dollar quoting因此它会吞噬在查询中的任何文本
select format('{%s: %s}',
to_json('query'::text),
to_json($query$
select *
from tests
where tests.id > 5
order by moment desc
$query$::text)
)::json;
format
---------------------------------------------------------------------------------------------------------------------
{"query": "\n select *\n from tests\n where tests.id > 5\n order by moment desc\n "}
http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-TABLE
http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-TABLE
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
The format function is just to make it easy on the eyes
格式功能只是为了方便看
http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER
http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER
