本文介绍了如何将sql-text转换为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.

推荐答案

要转换无类型的字符串文字(不是 用双引号引起来的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');

请注意,输入必须是字符串类型(textvarchar,...),而不是无类型文字.这就是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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-18 13:43