如何将json数组转换为文本数组?
此解决方法不起作用
CREATE FUNCTION json_array_castext(json) RETURNS text[] AS $f$
SELECT array_agg(x::text) FROM json_array_elements($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;
-- Problem:
SELECT 'hello'='hello'; -- true...
SELECT (json_array_castext('["hello","world"]'))[1] = 'hello'; -- false!
那么,如何获取文本的真实数组?
So, how to obtain real array of text?
PS:使用所谓的头等公民" JSONb,同样的问题.
PS: with the supposed "first class citizen" JSONb, the same problem.
在@OtoShavadze好的答案(注释已解决!)之后,是 PostgreSQL开发人员的清单:为什么x::text
不是强制转换?(使用pg 9.5) .6),为什么它不生成警告或错误?
after @OtoShavadze good answer (the comment solved!), a manifest for PostgreSQL developers: Why x::text
is not a cast? (using pg 9.5.6) and why it not generates an warning or an error?
尝试 json_array_elements_text 而不是json_array_elements
,并且您不需要显式转换为文本(x::text
),因此可以使用:
try json_array_elements_text instead of json_array_elements
, and you don't need explicit casting to text (x::text
), so you can use:
CREATE or replace FUNCTION json_array_castext(json) RETURNS text[] AS $f$
SELECT array_agg(x) FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;
关于其他问题
为什么x :: text不是强制转换?
Why x::text is not a cast?
这是强制转换的,因此,它不会产生任何错误,但是在将json字符串转换为这样的文本时:::text
,postgres将引号添加到值上.
This is cast and because of this, its not giving any error, but when casting json string to text like this: ::text
, postgres adds quotes to value.
仅出于测试目的,让您再次将功能更改为原始功能(如您所问的那样),然后尝试:
Just for testing purposes, lets change your function to original again (as it is in your question) and try:
SELECT
(json_array_castext('["hello","world"]'))[1] = 'hello',
(json_array_castext('["hello","world"]'))[1],
'hello'
如您所见,(json_array_castext('["hello","world"]'))[1]
给出的是"hello"
而不是hello
.这就是为什么在比较这些值时得到false
的原因.
As you see, (json_array_castext('["hello","world"]'))[1]
gives "hello"
instead of hello
. and this was why you got false
when comparing those values.