postgresql - 如何将文本转换为 jsonB

Question

在 Postgres(版本 9.6)中将任何文本(或 varchar)转换为 jsonB 类型的正确方法是什么？

例如，这里我使用了两种方法，但得到了不同的结果:

方法一:

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)

方法 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=# 

在这里，它被转换为字符串，而不是数组。为什么第二种方法不创建数组？

Answer 1

根据 Postgres documentation :

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.

恕我直言，您提供的是 JSON 格式的字符串，那么您应该使用第一种方法。

to_json('Fred said "Hi."'::text)  --> "Fred said \"Hi.\""

如果您尝试使用 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

但如果您之前将其转换为 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                                         |
+--------------------------------------------+