This workaround not works
此解决方法不起作用
CREATE FUNCTION json_array_castext(json) RETURNS text[] AS $f$
SELECT array_agg(x::text) FROM json_array_elements(
This workaround not works
此解决方法不起作用
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: with the supposed "first class citizen" JSONb, the same problem.
PS：与所谓的“一等公民”JSONb，同样的问题。
Edit: 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?
编辑：@OtoShavadze之后的好答案（评论解决了！），PostgreSQL开发人员的清单：为什么x :: text不是演员？ （使用第9.5.6页）以及为什么它不会产生警告或错误？
3 个解决方案
#1
2
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:
尝试json_array_elements_text而不是json_array_elements，你不需要显式转换为text（x :: text），所以你可以使用：
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;
For your additional question
对于您的其他问题
Why x::text is not a cast?
为什么x :: text不是演员？
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.
这是强制转换，因此，它没有给出任何错误，但是当将json字符串转换为文本时，例如：:: text，postgres会将值添加到引号中。
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'
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_array_castext（'[“hello”，“world”]'））[1]给出“hello”而不是hello。这就是为什么在比较这些值时你会弄错的原因。
#2
1
For this ugly behaviour of PostgreSQL, there are an ugly cast workaround, the operator #>>'{}':
对于PostgreSQL的丑陋行为，有一个丑陋的演员解决方法，运算符＃>>'{}'：
CREATE or replace FUNCTION json_array_castext(json) RETURNS text[] AS $f$
SELECT array_agg(x#>>'{}') FROM json_array_elements($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;
SELECT (json_array_castext('["hello","world"]'))[1] = 'hello'; -- true!
#3
1
Oto's answer was a lifesaver, but it did have one boundary case that had me racking my brain. Due to the lossy nature of the cast, it works perfectly except in the case where you've got an empty json array. In that case you would expect an empty array to be returned, but it actually returns nothing. As a workaround, if you just concatenate the return value with an empty array it will have no affect in cases where there is actually a return, but do the right thing when you've got an empty array. Here's the updated SQL functions (for both json and jsonb) that implement the workaround.
奥托的答案是一个救星，但它确实有一个边界案例，让我绞尽脑汁。由于演员阵容的有损性质，除了你有一个空的json数组之外，它的工作原理很完美。在这种情况下，你会期望返回一个空数组，但它实际上什么都不返回。作为一种解决方法，如果你只是将返回值与一个空数组连接起来，那么在实际有返回的情况下它将没有任何影响，但是当你有一个空数组时做正确的事情。这是实现变通方法的更新的SQL函数（对于json和jsonb）。
CREATE or replace FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;
CREATE or replace FUNCTION jsonb_array_casttext(jsonb) RETURNS text[] AS $f$
SELECT array_agg(x) || ARRAY[]::text[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;
There are a few peculiarities like this one that point to the rough edges at integrating a document database into a mature relational one, but Postgres does an admirable job at handling most of them.
像这样的一些特性指出了将文档数据库集成到成熟的关系数据库中的粗略边缘，但Postgres在处理大多数文档数据库方面做了令人钦佩的工作。
) t(x);
$f$ LANGUAGE sql IMMUTABLE;
-- Problem:
SELECT 'hello'='hello'; -- true...
SELECT (json_array_castext('["hello","world"]'))[1] = 'hello'; -- false!
CREATE 
