如何将json数组转换为文本数组? [英] How to cast json array to text array?

问题描述

此解决方法不起作用

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.

这篇关于如何将json数组转换为文本数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！