postgresql 如何将文本转换为 jsonB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41924784/
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
how do I convert text to jsonB
提问by Nulik
What is the proper way to convert any text (or varchar) to jsonBtype in Postgres (version 9.6) ?
将任何文本（或 varchar）转换jsonB为 Postgres（9.6 版）的正确方法是什么？
For example, here I am using two methods and I am getting different results:
例如，这里我使用了两种方法，但得到了不同的结果：
Method 1:
方法一：
dev=# select '[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::jsonb;
jsonb
----------------------------------------------------------------------------------------------
[{"field": 15, "value": "1", "operator": 0}, {"field": 15, "value": "2", "operator": 0}, 55]
(1 row)
Method 2 , which doesn't produce the desired results, btw:
Method 2 ，它不会产生预期的结果，顺便说一句：
dev=# select to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::text);
to_jsonb
----------------------------------------------------------------------------------------------------
"[{\"field\":15,\"operator\":0,\"value\":\"1\"},{\"field\":15,\"operator\":0,\"value\":\"2\"},55]"
(1 row)
dev=#
Here, it was converted to a string, not an array. Why doesn't the second method creates an array ?
在这里，它被转换为字符串，而不是数组。为什么第二种方法不创建一个数组？
回答by McNets
According to Postgres documentation:
根据Postgres 文档：
to_jsonb(anyelemnt)
Returns the value as json or jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.
to_jsonb(anyelemnt)
以 json 或 jsonb 形式返回值。数组和组合被（递归地）转换为数组和对象；否则，如果存在从类型到 json 的强制转换，则将使用强制转换函数来执行转换；否则，将产生一个标量值。对于除数字、布尔值或空值以外的任何标量类型，将使用文本表示形式，使其成为有效的 json 或 jsonb 值。
IMHO you are providing a JSON formatted string, then you should use the first method.
恕我直言，您提供的是 JSON 格式的字符串，那么您应该使用第一种方法。
to_json('Fred said "Hi."'::text) --> "Fred said \"Hi.\""
If you try to get an array of element using to_json(text) you'll get the next error:
如果您尝试使用 to_json(text) 获取元素数组，您将收到下一个错误：
select *
from jsonb_array_elements_text(to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::text));
cannot extract elements from a scalar
无法从标量中提取元素
But if you previously cast it to json:
但是，如果您之前将其转换为 json：
select *
from jsonb_array_elements_text(to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::json));
+--------------------------------------------+
| value |
+--------------------------------------------+
| {"field": 15, "value": "1", "operator": 0} |
+--------------------------------------------+
| {"field": 15, "value": "2", "operator": 0} |
+--------------------------------------------+
| 55 |
+--------------------------------------------+
回答by KerlW
If your text is just a json format text, you could just explicitly cast it to json/jsonb like this:
如果您的文本只是一个 json 格式的文本，您可以像这样显式地将其转换为 json/jsonb：
select '{"a":"b"}'::jsonb
选择 '{"a":"b"}'::jsonb
