如何将文本转换为 jsonB [英] how do I convert text to jsonB

问题描述

在 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:

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.

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));

cannot extract elements from a scalar

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！