如何将sql-text转换为jsonb-string? [英] How to convert sql-text to jsonb-string?

问题描述

似乎没有明显的方法:

• 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 json
• select '"a123"'::text::jsonb = BAD string because quoted
  check select '"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.

推荐答案

要转换无类型的

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:

• Postgres数据类型转换

要直接转换为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);

---

select '"a123"'::text::jsonb =引号错误的字符串

select '"a123"'::text::jsonb = BAD string because quoted

确切地说，结果不是 字符串，而是包含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';

我需要'123'和'a123'作为纯JSONb字符串.

I need '123' and 'a123' as pure JSONb strings.

所以:

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