如何将SQL文本转换为JSONB字符串？

Question

目前似乎没有明显的方法：

• select 'a123'::text::jsonb = 错误：类型 json 的输入语法无效
• select '"a123"'::text::jsonb = 字符串不正确，因为有引号
  请检查 select '"a123"'::text::jsonb = ('{"x":"a123"}'::jsonb)->'x'，
  看看非引号是正确的。
• select '123'::text::jsonb = ('{"x":123}'::jsonb)->'x'; = 不是字符串

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

---

注：这不是通用自动转换的重复问题。

- Peter Krauss

---

你想要实现什么？引用的版本是有效的jsonb字符串。在我看来，没有其他未引用的表示方法。 - S-Man

Answer 1

要将未包含在双引号中的未类型化字符串字面值转换为jsonb（或json），请使用to_jsonb()（或to_json()）函数：

SELECT to_jsonb(text 'a123');

请注意，输入必须是字符串类型（text，varchar等），而不是无类型文字。这就是Postgres知道您想要JSON字符串的方式。

上述的 text 'a123' 是将无类型文字转换为字符串的一种方法。还有其他方法：

• Postgres数据类型转换

对于直接转换为 json(b)，Postgres需要有效的JSON文字（带有双引号的字符串）：

SELECT '"a123"'::jsonb;  

为了将每个值转换为特定的JSON基本类型，您可以在转换之前进行条件转换。例如：

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 = 引号包裹的字符串是不正确的，原因是JSON格式化错误。

准确来说，结果并不是一个字符串，而是一个包含JSON字符串的jsonb值。如果想要以Postgres数据类型text的形式获取字符串，需要使用->>运算符：

select 'a123'::text  = ('{"x":"a123"}'::jsonb)->>'x'

或者（比较JSON值）：

select '"a123"'::jsonb = ('{"x":"a123"}'::jsonb)->'x';

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

因此：

SELECT '"123"'::jsonb, '"a123"'::jsonb;

两者都包含JSON 字符串。

这也可以使用：

SELECT '123'::jsonb;

...但其中包含一个JSON 数字。

但是这样做不起作用：

SELECT 'a123'::jsonb;  -- error

这是因为它不是有效的数字字面量。

手册中有一个映射表，在JSON原始类型和相应的PostgreSQL类型之间做映射

- Erwin Brandstetter

---

嗯...但问题是关于"jsonB-string"，而不是"如何转换为SQL-text"。 - Peter Krauss

我需要输入SQL字符串并将其保存为正确的JSONb类型到数据库中... 我需要告诉PostgreSQL：请将123视为JSONb字符串，请将a123视为JSONb字符串，等等。 - Peter Krauss

太好了！现在 select to_jsonb(text 'hello') = ('{"x":"hello"}'::jsonb)->'x' 已经可以正常工作，而且也适用于数字123。 - Peter Krauss

糟糕！在Postgresql版本12.5上，select p, to_jsonb(text p) from unnest(array['12','ab']::text[]) t(p)是一个语法错误。只能使用文字常量，如select to_jsonb(text 'p')。 - Peter Krauss

是的，该语法变体只适用于字面值转换。参见：https://dev59.com/3WYr5IYBdhLWcg3wb5vc#13676871 - Erwin Brandstetter