问题描述
似乎没有明显的方法:
-
select 'a123'::text::jsonb
= 错误:json类型的无效输入语法 -
select '"a123"'::text::jsonb
= BAD字符串,因为用引号
检查select '"a123"'::text::jsonb = ('{"x":"a123"}'::jsonb)->'x'
以确保未引用是正确的. -
select '123'::text::jsonb = ('{"x":123}'::jsonb)->'x';
= 没有字符串
select 'a123'::text::jsonb
= ERROR: invalid input syntax for type jsonselect '"a123"'::text::jsonb
= BAD string because quoted
checkselect '"a123"'::text::jsonb = ('{"x":"a123"}'::jsonb)->'x'
to see that non-quoted is the correct.select '123'::text::jsonb = ('{"x":123}'::jsonb)->'x';
= NOT string
我需要'123'和'a123'作为纯JSONb字符串.
I need '123' and 'a123' as pure JSONb strings.
PS:它不是通用自动转换的副本.
PS: it is not a duplicate of generic automatic-anything conversion.
推荐答案
要转换无类型的字符串文字(不是 用双引号引起来的jsonb
(或json
)),请使用 to_jsonb()
(或to_json()
)函数:
To convert untyped string literals, that are not wrapped in double-quotes to jsonb
(or json
), use the to_jsonb()
(or to_json()
) function:
SELECT to_jsonb(text 'a123');
请注意,输入必须是字符串类型(text
,varchar
,...),而不是无类型文字.这就是Postgres知道您想要JSON string 的方式.
Note that the input has to be a string type (text
, varchar
, ...), not an untyped literal. That's how Postgres knows you want a JSON string.
上面的text 'a123'
是转换无类型文字的一种方法.还有其他:
The above text 'a123'
is one way to cast an untyped literal. There are others:
要直接转换为json(b)
,Postgres需要有效的JSON文字(带双引号的 strings ):
For a direct cast to json(b)
, Postgres expects valid JSON literals (with double-quoted strings):
SELECT '"a123"'::jsonb;
要将每个值转换为特定的JSON原语,可以在转换之前有条件地进行强制转换.示例:
To translate each value to a specific JSON primitive, you can cast conditionally before the conversion. Example:
SELECT p, CASE WHEN i>2 THEN to_jsonb(p::numeric) ELSE to_jsonb(p) END AS x
FROM unnest('{ab,12,12,1.2}'::text[]) WITH ORDINALITY t(p,i);
确切地说,结果不是 字符串,而是包含JSON字符串的jsonb
值.若要将字符串作为Postgres数据类型text
,您需要->>
运算符:
To be precise, the result is not a string, but a jsonb
value containing a JSON string. To get the string as Postgres data type text
, you need the ->>
operator:
select 'a123'::text = ('{"x":"a123"}'::jsonb)->>'x'
或(比较JSON值):
Or (comparing JSON values):
select '"a123"'::jsonb = ('{"x":"a123"}'::jsonb)->'x';
所以:
SELECT '"123"'::jsonb, '"a123"'::jsonb;
两者都包含JSON 字符串 .
Both contain JSON strings.
这也有效:
SELECT '123'::jsonb;
..但包含JSON 数字 .
.. but contains a JSON numeric.
但这不起作用:
SELECT 'a123'::jsonb; -- error
..因为它不是有效的数字文字.
.. because it's not a valid numeric literal.
该手册具有 JSON基本类型和对应的PostgreSQL类型
The manual has a table of mappings between JSON Primitive Types and Corresponding PostgreSQL Types
这篇关于如何将sql-text转换为jsonb-string?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!