问题描述
在 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 行)
方法 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,\"值\":\"2\"},55]"(1 行)开发=#
在这里,它被转换为字符串,而不是数组.为什么第二种方法不创建数组?
根据 Postgres 文档:
to_jsonb(anyelemnt)
以 json 或 jsonb 形式返回值.数组和复合是转换(递归)为数组和对象;否则,如果有从类型转换为 json,转换函数将用于执行转换;否则,将产生一个标量值.对于任何数字、布尔值或空值以外的标量类型,文本表示将被使用,以这样一种方式,它是一个有效的 json或 jsonb 值.
恕我直言,您提供的是 JSON 格式的字符串,那么您应该使用第一种方法.
to_json('Fred said "Hi."'::text) -->弗雷德说‘嗨.’"
如果您尝试使用 to_json(text) 获取元素数组,则会出现下一个错误:
选择 *from jsonb_array_elements_text(to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::文本));
无法从标量中提取元素
但是如果您之前将其转换为 json:
选择 *from jsonb_array_elements_text(to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::json));+--------------------------------------------+|价值 |+--------------------------------------------+|{"field": 15, "value": "1", "operator": 0} |+----------------------------------------------------+|{"field": 15, "value": "2", "operator": 0} |+----------------------------------------------------+|55 |+----------------------------------------------------+
What is the proper way to convert any text (or varchar) to jsonB
type in Postgres (version 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:
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 ?
According to Postgres documentation:
IMHO you are providing a JSON formatted string, then you should use the first method.
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:
select *
from jsonb_array_elements_text(to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::text));
But if you previously cast it to 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 |
+--------------------------------------------+
这篇关于如何将文本转换为 jsonB的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!